﻿using HCQ2_Common.SQL;
using HCQ2_Model;
using HCQ2_Model.DocModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using HCQ2_Common;
using HCQ2WebAPI_Logic.Model;
using HCQ2_Model.DocModel.APIResultModel;
using HCQ2_Model.DocModel.APIModel;
using HCQ2_Model.Pay;
using HCQ2_Model.VR.Model;
using HCQ2_Model.VRDevice.ResultModel;
using HCQ2_Model.VRDevice.ParamModel;
using HCQ2_Model.VRDD.OutputDto._0730;
using HCQ2_Model.VR.Params;
using HCQ2_Model.VRDD.InputDto._0730;
using HCQ2_Model.VRDD.OutputDto._210908;
using HCQ2_Model.VRDD.InputDto._210908;
using HCQ2_Model.VRQDMeta.OutputDto;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_DocumentInfoDAL:HCQ2_IDAL.IT_DocumentInfoDAL
    {
        /// <summary>
        ///  参数
        /// </summary>
        private Dictionary<string, object> _param = new Dictionary<string, object>();
        /// <summary>
        ///  sql语句
        /// </summary>
        private StringBuilder sb = new StringBuilder();
        private List<int> listFolder = new List<int>();
        //*******************************************奇点元宇宙***********************************************
        /// <summary>
        ///  获取奇点元宇宙【应用】列表
        /// </summary>
        /// <param name="param">查询参数</param>
        /// <param name="total">返回总条数</param>
        /// <returns></returns>
        public List<MetaListResult> GetQDMetaList(QDMetaParam param, out int total)
        {
            sb?.Clear();
            sb.Append($@"(SELECT i.*,l.folder_id,l.folder_path,l.folder_name,ROW_NUMBER() OVER(ORDER BY file_order DESC)num FROM 	
	        (SELECT file_id,folder_id,folder_path,folder_name=(SELECT folder_name FROM dbo.T_DocumentFolder
	        WHERE folder_id=n.folder_id) FROM dbo.T_DocumentFolderRelation n WHERE 1=1 ");
            if (string.IsNullOrEmpty(param.operatorType))
            {
                //管理列表
                if (param.inChild.Equals("no"))
                    sb.Append($" AND folder_id={param.folder_id} ");
                else
                    sb.Append($" AND folder_path LIKE '{param.folder_path}%' ");
            }           
            sb.Append($@")l INNER JOIN (SELECT file_id,file_name,file_order,folder_num,file_code,file_money,is_recommend,pag_num,file_size,
            file_status,approve_name,CONVERT(varchar(100),approve_time,120) AS approve_time,down_number,pay_num,vr_star,browse_num,
            finish_time,attach_url,create_name,CONVERT(varchar(100),create_time,120) AS create_time,update_name,file_note,
            CONVERT(varchar(100),update_time,120) AS update_time FROM dbo.T_DocumentInfo WHERE page_type='QDMetaProgram'  ");
            //是否待审核
            if (!string.IsNullOrEmpty(param.operatorType) && param.operatorType.Equals("approve"))
                sb.Append($" AND file_status={param.fileStatus} ");
            else
                sb.Append($" AND file_status=5 ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (file_name LIKE '{param.keyword}%' OR file_code LIKE '{param.keyword}%' OR file_note LIKE '{param.keyword}%') ");
            if (param.is_recommend > -1)
                sb.Append($"AND is_recommend={param.is_recommend} ");
            sb.Append(")i ON i.file_id = l.file_id)t ");
            total = Helper.ToInt(SqlHelper.ExecuteScalar("SELECT COUNT(0) FROM " + sb.ToString()));
            return SqlHelper.ExecuteList<MetaListResult>($"SELECT  TOP {param.rows} * FROM " + sb.ToString() + $" WHERE t.num>{(param.page - 1) * param.rows}");
        }
        /// <summary>
        ///  获取数据
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public MetaListResult GetProgramData(int file_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT i.*,l.folder_id,l.folder_path,l.folder_name FROM 	
            (SELECT file_id,folder_id,folder_path,folder_name=(SELECT folder_name FROM dbo.T_DocumentFolder
            WHERE folder_id=n.folder_id) FROM dbo.T_DocumentFolderRelation n WHERE file_id={file_id})l INNER JOIN (SELECT 
            file_status,approve_name,CONVERT(varchar(100),approve_time,120) AS approve_time,down_number,pay_num,vr_star,browse_num,
            file_id,file_name,file_order,folder_num,file_code,file_money,is_recommend,pag_num,file_size,file_note,
            finish_time,attach_url,create_name,CONVERT(varchar(100),create_time,120) AS create_time,update_name,
            CONVERT(varchar(100),update_time,120) AS update_time FROM dbo.T_DocumentInfo WHERE file_id={file_id} )i ON i.file_id = l.file_id;");
            return SqlHelper.ExecuteList<MetaListResult>(sb.ToString())?.FirstOrDefault();
        }
        /// <summary>
        ///  获取奇点元宇宙【房间】列表
        /// </summary>
        /// <param name="param">查询参数</param>
        /// <param name="total">返回总条数</param>
        /// <returns></returns>
        public List<MetaRoomResult> GetQDMetaRoomList(QDMetaParam param, out int total)
        {
            sb?.Clear();
            sb.Append($@"(SELECT i.*,l.folder_id,l.folder_path,l.folder_name,ROW_NUMBER() OVER(ORDER BY file_order DESC)num FROM 	
	        (SELECT file_id,folder_id,folder_path,folder_name=(SELECT folder_name FROM dbo.T_DocumentFolder
	        WHERE folder_id=n.folder_id) FROM dbo.T_DocumentFolderRelation n WHERE ");
            if (param.inChild.Equals("no"))
                sb.Append($" folder_id={param.folder_id} ");
            else
                sb.Append($" folder_path LIKE '{param.folder_path}%' ");
            sb.Append($@")l INNER JOIN (SELECT file_id,gps_x,gps_y,doc_number,programId,file_name,file_order,folder_num,file_code,file_money,is_recommend,pag_num,file_size,
            file_status,approve_name,CONVERT(varchar(100),approve_time,120) AS approve_time,down_number,pay_num,vr_star,browse_num,
            qr_code as roomPwd,CONVERT(varchar(100),game_start,120) AS game_start,CONVERT(varchar(100),game_end,120) AS game_end,
            finish_time,attach_url,create_name,CONVERT(varchar(100),create_time,120) AS create_time,update_name,file_note,
            CONVERT(varchar(100),update_time,120) AS update_time FROM dbo.T_DocumentInfo WHERE page_type='QDMetaRoom' AND file_status=5 ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (file_name LIKE '{param.keyword}%' OR file_code LIKE '{param.keyword}%' OR file_note LIKE '{param.keyword}%') ");
            if (param.is_recommend > -1)
                sb.Append($"AND is_recommend={param.is_recommend} ");
            sb.Append(")i ON i.programId = l.file_id)t ");
            total = Helper.ToInt(SqlHelper.ExecuteScalar("SELECT COUNT(0) FROM " + sb.ToString()));
            return SqlHelper.ExecuteList<MetaRoomResult>($"SELECT  TOP {param.rows} * FROM " + sb.ToString() + $" WHERE t.num>{(param.page - 1) * param.rows}");
        }
        /// <summary>
        ///  获取数据【房间】
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public MetaRoomResult GetRoomData(int file_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT i.*,l.folder_id,l.folder_path,l.folder_name FROM 	
            (SELECT file_id,folder_id,folder_path,folder_name=(SELECT folder_name FROM dbo.T_DocumentFolder
            WHERE folder_id=n.folder_id) FROM dbo.T_DocumentFolderRelation n WHERE file_id={file_id})l INNER JOIN (SELECT 
            file_status,approve_name,CONVERT(varchar(100),approve_time,120) AS approve_time,down_number,pay_num,vr_star,browse_num,
            roomPwd,CONVERT(varchar(100),game_start,120) AS game_start,CONVERT(varchar(100),game_end,120) AS game_end,
            file_id,file_name,file_order,folder_num,file_code,file_money,is_recommend,pag_num,file_size,file_note,
            finish_time,attach_url,create_name,CONVERT(varchar(100),create_time,120) AS create_time,update_name,
            CONVERT(varchar(100),update_time,120) AS update_time FROM dbo.T_DocumentInfo WHERE file_id={file_id} )i ON i.file_id = l.file_id;");
            return SqlHelper.ExecuteList<MetaRoomResult>(sb.ToString())?.FirstOrDefault();
        }
        //*******************************************VR楼宇***********************************************
        /// <summary>
        ///  获取设备类别
        /// </summary>
        /// <param name="param">查询参数</param>
        /// <param name="total">返回总条数</param>
        /// <returns></returns>
        public List<VRBuildResult> GetVRBuildList(VRdeviceParam param, out int total)
        {
            sb?.Clear();
            sb.Append($@"(SELECT info.*,folder.folder_id,folder.folder_name,folder.folder_path,price.price,u.user_name,u.user_phone,
            (ROW_NUMBER() OVER(ORDER BY info.file_name))AS rowNum FROM 
            (SELECT file_id,file_name,file_note,file_code,file_status,qr_code,issue_name,CONVERT(varchar(100),create_time,120) AS create_time,create_name,file_order,doc_number,
             CONVERT(varchar(100),update_time,120) AS update_time,update_name,alias_name,programId FROM dbo.T_DocumentInfo 
            WHERE page_type='VRBuilding' AND file_status=5 AND if_remove=0 ");
            if (param.file_status > -1)
                sb.Append($" AND file_status = {param.file_status} ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (file_name LIKE '{param.keyword}%' OR file_code LIKE '{param.keyword}%' OR file_note LIKE '{param.keyword}%') ");
            sb.Append($")info INNER JOIN (SELECT file_id, folder_id FROM dbo.T_DocumentFolderRelation WHERE ");
            if (param.inChild.Equals("no"))
                sb.Append($" folder_id={param.folder_id} ");
            else
                sb.Append($" folder_path LIKE '{param.folder_path}%' ");
            sb.Append($@" )rel ON rel.file_id = info.file_id INNER JOIN(SELECT folder_id,folder_name,folder_path FROM dbo.T_DocumentFolder)folder ON rel.folder_id=folder.folder_id left join(select user_guid,user_name,user_phone from T_User)u on info.issue_name=u.user_guid LEFT JOIN (SELECT MIN(ori_price)AS price,file_id FROM dbo.T_VRGoodDisiPrice GROUP BY file_id)price ON price.file_id = info.file_id)infos ");
            total = Helper.ToInt(SqlHelper.ExecuteScalar("SELECT COUNT(0) AS num FROM " + sb.ToString()));
            DataTable dt = SqlHelper.ExecuteDataTable($"SELECT TOP {param.rows} * FROM " + sb.ToString() + $" WHERE infos.rowNum>{(param.page - 1) * param.rows} order by infos.file_order desc;");
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<VRBuildResult>(dt);
        }
        //*******************************************VR设备***********************************************
        /// <summary>
        ///  获取设备类别
        /// </summary>
        /// <param name="param">查询参数</param>
        /// <param name="total">返回总条数</param>
        /// <returns></returns>
        public List<VRdeviceResult> GetVRDeviceList(VRdeviceParam param, out int total)
        {
            sb?.Clear();
            sb.Append($@"(SELECT info.*,folder.folder_id,folder.folder_name,folder.folder_path,price.price,(ROW_NUMBER() OVER(ORDER BY info.file_name))AS rowNum FROM 
            (SELECT file_id,file_name,file_note,file_code,file_status,qr_code,CONVERT(varchar(100),create_time,120) AS create_time,create_name,
             CONVERT(varchar(100),update_time,120) AS update_time,update_name FROM dbo.T_DocumentInfo WHERE page_type='VRDevice' AND if_remove=0 ");
            if (param.file_status > -1)
                sb.Append($" AND file_status = {param.file_status} ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (file_name LIKE '{param.keyword}%' OR file_code LIKE '{param.keyword}%' OR file_note LIKE '{param.keyword}%') ");
            sb.Append($")info INNER JOIN (SELECT file_id, folder_id FROM dbo.T_DocumentFolderRelation WHERE ");
            if (param.inChild.Equals("no"))
                sb.Append($" folder_id={param.folder_id} ");
            else
                sb.Append($" folder_path LIKE '{param.folder_path}%' ");
            sb.Append($@" )rel ON rel.file_id = info.file_id INNER JOIN(SELECT folder_id,folder_name,folder_path FROM dbo.T_DocumentFolder)folder ON rel.folder_id=folder.folder_id LEFT JOIN (SELECT MIN(ori_price)AS price,file_id FROM dbo.T_VRGoodDisiPrice GROUP BY file_id)price ON price.file_id = info.file_id)infos ");
            total = Helper.ToInt(SqlHelper.ExecuteScalar("SELECT COUNT(0) AS num FROM " + sb.ToString()));
            DataTable dt = SqlHelper.ExecuteDataTable($"SELECT TOP {param.rows} * FROM " + sb.ToString() + $" WHERE infos.rowNum>{(param.page - 1) * param.rows}");
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<VRdeviceResult>(dt);
        }
        /// <summary>
        ///  获取设备信息
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public VRdeviceResult GetVRDevicePrice(int file_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT info.*,folder.folder_id,folder.folder_name,folder.folder_path FROM 
            (SELECT file_id,file_name,file_order,file_note,file_code,file_status,qr_code,CONVERT(varchar(100),create_time,120) AS create_time,create_name,
             CONVERT(varchar(100),update_time,120) AS update_time,update_name FROM dbo.T_DocumentInfo WHERE file_id={file_id})info INNER JOIN 
             (SELECT file_id, folder_id FROM dbo.T_DocumentFolderRelation WHERE  file_id={file_id})
             rel ON rel.file_id = info.file_id INNER JOIN(SELECT folder_id,folder_name,folder_path FROM dbo.T_DocumentFolder)folder ON 
             rel.folder_id=folder.folder_id;");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<VRdeviceResult>(dt).FirstOrDefault();
        }
        //********************************************end*************************************************


        #region 0. 我的文档
        /// <summary>
        /// 0. 我的文档
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<DocTreeResultModel> GetTableByOwnDoc(DocTableParamModel model,int user_id)
        {
            string _sb = "";
            List<int> fList = GetChildFileID(model.folder_id, user_id);
            sb?.Clear();
            sb.Append(string.Format(@"SELECT TOP  {0} info.file_id,info.file_name,info.create_id,info.alias_name,info.file_type,info.file_size,info.issue_start,info.issue_end,info.create_time,info.create_name,info.file_note,info.url,info.doc_type,info.mater_type,doc_number,info.file_money,info.file_classify,info.file_status,info.browse_num,info.quote_num,info.play_num,info.is_recommend,info.approve_time,info.update_time,info.folder_author,info.pro_type,info.pro_type_text,info.pro_form,info.vr_star,info.down_number,info.folder_limit,info.org_unit,info.file_code,info.folder_num,ration.* FROM
            (SELECT file_id,folder_id,folder_path,folder_name=(SELECT folder_name FROM dbo.T_DocumentFolder folder WHERE rel.folder_id=folder.folder_id) FROM T_DocumentFolderRelation rel WHERE create_id={1} ", model.rows,user_id));
            //是否查询我的全部
            if (string.IsNullOrEmpty(model.file_status) || !model.file_status.Equals("10"))
            {
                if (model.inChild.Equals("no"))
                    sb.AppendFormat(" AND folder_id in({0}) ", model.folder_id);
                else
                {
                    if (!string.IsNullOrEmpty(model.folder_path))
                        sb.AppendFormat(" AND folder_path LIKE '{0}%' ", model.folder_path);
                    else
                        sb.AppendFormat(" AND folder_id in({0}) ", string.Join(",", fList));
                }
            }
            else
                _sb = $" AND page_type='{model.goodType}' ";
            if (model.file_status == "6")
                sb.AppendFormat(") ration RIGHT JOIN ");
            else
                sb.AppendFormat(") ration INNER JOIN ");
            sb.AppendFormat($@" (SELECT file_id, file_name, alias_name, file_type, file_size = (CASE WHEN file_size > 999 THEN CAST(CAST(file_size / 1024 AS DECIMAL(18, 2)) AS NVARCHAR(20)) + 'M' ELSE CAST(file_size AS NVARCHAR(20)) + 'KB' END),
            CONVERT(varchar(100), issue_start, 23) AS issue_start, CONVERT(varchar(100), issue_end, 23) AS issue_end, CONVERT(varchar(100), create_time, 23) AS create_time,CONVERT(varchar(100),approve_time,120) AS approve_time,CONVERT(varchar(100),update_time,120) AS update_time,folder_author,pro_type,dbo.fn_selectdict(pro_type) AS pro_type_text,pro_form,vr_star,folder_limit,
            issue_name, create_name, file_note, attach_url AS url, ROW_NUMBER() OVER(order by file_id asc) as DispOrder, create_id, doc_type, dbo.fn_selectdict(mater_type) as mater_type,doc_number, file_money, down_number, file_classify, file_status,browse_num,quote_num,play_num,is_recommend,video_url,org_unit,file_code,folder_num FROM dbo.T_DocumentInfo WHERE if_remove = 0 AND create_id={user_id} {_sb} ");
            //文档名称
            if (!string.IsNullOrEmpty(model.keyword))
                sb.Append(string.Format(" AND (file_name like '%{0}%' OR create_name like '%{0}%') ",model.keyword));
            //文档状态
            if (!string.IsNullOrEmpty(model.file_status) && model.file_status!="-1" && model.file_status != "10")
                sb.Append(string.Format(" AND file_status={0} ", model.file_status));
            sb.Append(string.Format(@") info ON ration.file_id=info.file_id where info.DispOrder>{0};", (model.page - 1) * model.rows));
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text);
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<DocTreeResultModel>(dt);
        }
        /// <summary>
        ///  0. 我的文档 数量统计
        /// </summary>
        /// <param name="model"></param>
        /// <param name="user_id">用户id</param>folder_id in(0)
        /// <returns></returns>
        public int GetTableByOwnDocCount(DocTableParamModel model, int user_id)
        {
            string _sb = "";
            List<int> fList = GetChildFileID(model.folder_id, user_id);
            sb?.Clear();
            sb.AppendFormat(@"SELECT COUNT(*) FROM
            (SELECT file_id FROM T_DocumentFolderRelation WHERE create_id={0} ",user_id);
            //是否查询我的全部
            if (string.IsNullOrEmpty(model.file_status) || !model.file_status.Equals("10"))
            {
                if (model.inChild.Equals("no"))
                    sb.AppendFormat(" AND folder_id in({0}) ", model.folder_id);
                else
                {
                    if (!string.IsNullOrEmpty(model.folder_path))
                        sb.AppendFormat(" AND folder_path LIKE '{0}%' ", model.folder_path);
                    else
                        sb.AppendFormat(" AND folder_id in({0}) ", string.Join(",", fList));
                }
            }
            else
                _sb = $" AND page_type='{model.goodType}' ";
            if (model.file_status == "6")
                sb.AppendFormat(") ration RIGHT JOIN ");
            else
                sb.AppendFormat(") ration INNER JOIN ");
            sb.AppendFormat($"  (SELECT file_id FROM dbo.T_DocumentInfo WHERE if_remove=0 AND create_id={user_id} {_sb} ");
            //文档名称
            if (!string.IsNullOrEmpty(model.keyword))
                sb.Append(string.Format(" AND (file_name like '%{0}%' OR create_name like '%{0}%') ",model.keyword));
            //文档状态
            if (!string.IsNullOrEmpty(model.file_status) && model.file_status != "-1" && model.file_status != "10")
                sb.Append(string.Format(" AND file_status={0} ", model.file_status));
            sb.Append(@") info ON ration.file_id=info.file_id;");           
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text));
        }
        #endregion

        #region 1. 我的分享（分享给别人的）
        /// <summary>
        /// 1. 我的分享
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<DocTreeResultModel> GetTableByOwnShareDoc(DocTableParamModel model, int user_id)
        {
            sb?.Clear();
            sb.Append(string.Format(@"SELECT TOP {0} info.file_id,info.file_name,info.create_id,info.alias_name,info.file_type,info.file_size,info.issue_start,info.issue_end,info.create_time,info.create_name,info.file_note,info.url,info.doc_type,info.mater_type,doc_number,info.file_money,info.file_classify,info.file_status,info.browse_num,info.quote_num,info.play_num,info.is_recommend,info.approve_time,info.update_time,info.folder_author,info.pro_type,info.pro_type_text,info.pro_form,info.vr_star,info.down_number,info.folder_limit,info.org_unit,info.file_code,info.folder_num,folder.* FROM
            (SELECT file_id FROM T_DocumentSetType WHERE share_id={1}) share INNER JOIN", model.rows, user_id));
            if (!string.IsNullOrEmpty(model.folder_path))
                sb.AppendFormat(@"(select file_id,folder_id,folder_path,folder_name=(SELECT folder_name FROM dbo.T_DocumentFolder folder WHERE rel.folder_id=folder.folder_id)  from T_DocumentFolderRelation rel where folder_path LIKE '{0}%')folder on share.file_id=folder.file_id INNER JOIN ", model.folder_path);
            sb.AppendFormat(@"
            (SELECT file_id,file_name,alias_name,file_type,file_size=(CASE WHEN file_size>999 THEN CAST(CAST(file_size/1024 AS DECIMAL(18,2)) AS NVARCHAR(20))+'M' ELSE CAST(file_size AS NVARCHAR(20))+'KB' END),
            CONVERT(varchar(100),issue_start,23) AS issue_start,CONVERT(varchar(100),issue_end,23) AS issue_end,CONVERT(varchar(100),create_time,23) AS create_time,CONVERT(varchar(100),approve_time,120) AS approve_time,CONVERT(varchar(100),update_time,120) AS update_time,folder_author,pro_type,dbo.fn_selectdict(pro_type) AS pro_type_text,pro_form,vr_star,folder_limit,
            issue_name,create_name,file_note,attach_url AS url,ROW_NUMBER() OVER(order by file_id asc) as DispOrder,create_id,doc_type,dbo.fn_selectdict(mater_type) as mater_type,doc_number,file_money,down_number,file_classify,file_status,browse_num,quote_num,play_num,is_recommend,video_url,org_unit,file_code,folder_num FROM dbo.T_DocumentInfo WHERE if_remove=0 ");
            //文档名称
            if (!string.IsNullOrEmpty(model.keyword))
                sb.Append(string.Format(" AND (file_name like '%{0}%' OR create_name like '%{0}%') ",model.keyword));
            if (!string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE)  AND issue_start<=CAST('{1}' AS DATE) ", model.issue_start, model.issue_end));
            else if (!string.IsNullOrEmpty(model.issue_start) && string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE) ", model.issue_start));
            else if (string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_end<=CAST('{0}' AS DATE) ", model.issue_end));
            sb.Append(string.Format(@")info ON share.file_id=info.file_id WHERE info.DispOrder>{0};", (model.page - 1) * model.rows));
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text);
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<DocTreeResultModel>(dt);
        }
        /// <summary>
        /// 1. 我的分享 数量统计
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int GetTableByOwnShareDocCount(DocTableParamModel model, int user_id)
        {
            sb?.Clear();
            sb.AppendFormat(@"SELECT COUNT(*) FROM
            (SELECT file_id FROM T_DocumentSetType WHERE share_id={0} ) share INNER JOIN ",user_id);
            //if (model.folder_id > 0)
            //    sb.AppendFormat(" AND folder_id=@folder_id");
            if (!string.IsNullOrEmpty(model.folder_path))
                sb.AppendFormat(" (select * from T_DocumentFolderRelation where folder_path LIKE '{0}%')folder on share.file_id=folder.file_id INNER JOIN ", model.folder_path);
            sb.AppendFormat(" (SELECT file_id FROM dbo.T_DocumentInfo WHERE if_remove = 0 ");
            //文档名称
            if (!string.IsNullOrEmpty(model.keyword))
                sb.Append(string.Format(" AND (file_name like '%{0}%' OR create_name like '%{0}%') ",model.keyword));
            if (!string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE)  AND issue_start<=CAST('{1}' AS DATE) ", model.issue_start, model.issue_end));
            else if (!string.IsNullOrEmpty(model.issue_start) && string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE) ", model.issue_start));
            else if (string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_end<=CAST('{0}' AS DATE) ", model.issue_end));
            sb.Append(@")info ON share.file_id=info.file_id;");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text));
        }
        #endregion

        #region 2. 我的收藏（收藏夹）
        /// <summary>
        /// 2. 我的收藏
        /// </summary> 
        /// <param name="model"></param>
        /// <returns></returns>
        public List<DocTreeResultModel> GetTableShareByOwnDoc(DocTableParamModel model, int user_id)
        {
            sb?.Clear();
            sb.Append(string.Format(@"SELECT TOP {0} info.file_id,info.file_name,info.alias_name,info.file_type,info.file_size,info.issue_start,info.issue_end,info.create_time,info.create_name,info.file_note,info.url,info.doc_type,info.mater_type,doc_number,info.file_money,info.file_classify,info.file_status,info.browse_num,info.quote_num,info.play_num,info.is_recommend,info.approve_time,info.update_time,info.folder_author,info.pro_type,info.pro_type_text,info.pro_form,info.vr_star,info.down_number,info.folder_limit,info.org_unit,info.file_code,info.folder_num,folder_id=0,folder_name='',folder_path='' FROM (SELECT * FROM (", model.rows));
            if (!string.IsNullOrEmpty(model.folder_path))
                sb.AppendFormat(@"SELECT good.file_id FROM 
                (SELECT file_id,folder_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE '{0}%')rel INNER JOIN
                (SELECT file_id FROM dbo.T_VRFavoriteGoods WHERE user_id={1})good ON good.file_id = rel.file_id ", model.folder_path, user_id);
            else
                sb.AppendFormat(@"SELECT good.file_id FROM 
                (SELECT file_id,folder_id FROM dbo.T_DocumentFolderRelation WHERE folder_id={0})rel INNER JOIN
                (SELECT file_id FROM dbo.T_VRFavoriteGoods WHERE user_id={1})good ON good.file_id = rel.file_id ", model.folder_id, user_id);
            sb.AppendFormat(@")a
                 ) share INNER JOIN
                 (SELECT file_id,file_name,alias_name,file_type,file_size=(CASE WHEN file_size>999 THEN CAST(CAST(file_size/1024 AS DECIMAL(18,2)) AS NVARCHAR(20))+'M' ELSE CAST(file_size AS NVARCHAR(20))+'KB' END),
                CONVERT(varchar(100),issue_start,23) AS issue_start,CONVERT(varchar(100),issue_end,23) AS issue_end,CONVERT(varchar(100),create_time,23) AS create_time,CONVERT(varchar(100),approve_time,120) AS approve_time,CONVERT(varchar(100),update_time,120) AS update_time,folder_author,pro_type,dbo.fn_selectdict(pro_type) AS pro_type_text,pro_form
,vr_star,folder_limit,
                issue_name,create_name,file_note,attach_url AS url,ROW_NUMBER() OVER(order by file_id asc) as DispOrder,create_id,doc_type,dbo.fn_selectdict(mater_type) as mater_type,doc_number,file_money,down_number,file_classify,file_status,browse_num,quote_num,play_num,is_recommend,video_url,org_unit,file_code,folder_num FROM dbo.T_DocumentInfo WHERE if_remove=0");
            //文档名称
            if (!string.IsNullOrEmpty(model.keyword))
                sb.Append(string.Format(" AND (file_name like '%{0}%' OR create_name like '%{0}%') ",model.keyword));
            if (!string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE)  AND issue_start<=CAST('{1}' AS DATE) ", model.issue_start, model.issue_end));
            else if (!string.IsNullOrEmpty(model.issue_start) && string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE) ", model.issue_start));
            else if (string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_end<=CAST('{0}' AS DATE) ", model.issue_end));
            sb.Append(string.Format(@") info
                  ON info.file_id=share.file_id WHERE info.DispOrder>{0};", (model.page - 1) * model.rows));           
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text);
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<DocTreeResultModel>(dt);
        }
        /// <summary>
        /// 2. 我的收藏 数量统计
        /// </summary> 
        /// <param name="model"></param>
        /// <returns></returns>
        public int GetTableShareByOwnDocCount(DocTableParamModel model, int user_id)
        {
            sb?.Clear();
            sb.Append(string.Format(@"SELECT COUNT(*) FROM (SELECT * FROM ("));
            if (!string.IsNullOrEmpty(model.folder_path))
                sb.AppendFormat(@"SELECT good.file_id FROM 
                (SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE '{0}%')rel INNER JOIN
                (SELECT file_id FROM dbo.T_VRFavoriteGoods WHERE user_id={1})good ON good.file_id = rel.file_id ", model.folder_path, user_id);
            else
                sb.AppendFormat(@"SELECT good.file_id FROM 
                (SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_id={0})rel INNER JOIN
                (SELECT file_id FROM dbo.T_VRFavoriteGoods WHERE user_id={1})good ON good.file_id = rel.file_id ", model.folder_id, user_id);
            sb.AppendFormat(@" )a ) share INNER JOIN  (SELECT file_id FROM dbo.T_DocumentInfo WHERE if_remove=0");
                //文档名称
                if (!string.IsNullOrEmpty(model.keyword))
                sb.Append(string.Format(" AND (file_name like '%{0}%' OR create_name like '%{0}%') ",model.keyword));
            if (!string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE)  AND issue_start<=CAST('{1}' AS DATE) ", model.issue_start, model.issue_end));
            else if (!string.IsNullOrEmpty(model.issue_start) && string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE) ", model.issue_start));
            else if (string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_end<=CAST('{0}' AS DATE) ", model.issue_end));
            sb.Append(@") info
                  ON info.file_id=share.file_id;");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text));
        }
        #endregion

        #region 3. 公用文档
        /// <summary>
        /// 3. 公用文档
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<DocTreeResultModel> GetTablePublicDoc(DocTableParamModel model, int user_id,int fileStatus = 5)
        {
            List<int> fList = GetChildFileID(model.folder_id, user_id);
            sb?.Clear();
            sb.Append(string.Format(@"SELECT TOP {0} * FROM( SELECT  ROW_NUMBER() OVER(order by info.approve_time DESC) as DispOrder,info.isHot,info.isSoldOut,info.file_id,info.file_name,info.create_id,info.alias_name,info.file_type,info.file_size,info.issue_start,info.issue_end,info.create_time,info.create_name,info.file_note,info.url,info.doc_type,info.mater_type,doc_number,info.file_money,info.file_classify,info.file_status,info.browse_num,info.quote_num,info.play_num,info.is_recommend,info.approve_time,info.update_time,info.folder_author,info.pro_type,info.pro_type_text,info.pro_form,info.vr_star,info.down_number,info.folder_limit,info.org_unit,info.file_code,info.folder_num,folder.* FROM (SELECT file_id,folder_id FROM dbo.T_DocumentFolderRelation WHERE 1=1", model.rows));
            //是否包含子目录
            if (model.inChild.Equals("no"))
                sb.AppendFormat(" AND folder_id in({0}) ", model.folder_id);
            else
            {
                if (!string.IsNullOrEmpty(model.folder_path))
                    sb.AppendFormat(@" AND folder_path LIKE '{0}%' ", model.folder_path);
                else
                    sb.AppendFormat(" AND folder_id in({0}) ", string.Join(",", fList));
            }
            //价格
            StringBuilder sourceType = new StringBuilder();
            if (model.sourceType > 0)
                sourceType.AppendFormat(" AND file_status={0} ", model.sourceType);
            else
                sourceType.AppendFormat(" AND (file_status=3 OR file_status=5) ");
            //是否热门推荐
            if(model.isHot>-1)
                sourceType.Append($" AND isHot={model.isHot} ");
            //产品类别
            if (model.pro_type > -1)
                sourceType.AppendFormat(" AND pro_type={0} ", model.pro_type);
            //适合分类
            if(!string.IsNullOrEmpty(model.file_classify))
                sourceType.AppendFormat(" AND file_classify='{0}' ", model.file_classify);
            //是否推荐
            if(model.is_recommend>-1)
                sourceType.AppendFormat(" AND is_recommend={0} ", model.is_recommend);
            //所属单位
            if (!string.IsNullOrEmpty(model.org_unit))
                sourceType.AppendFormat(" AND unit_code='{0}' ", model.org_unit);
            //素材格式
            if(!string.IsNullOrEmpty(model.mater_type))
                sourceType.AppendFormat(" AND mater_type='{0}' ", model.mater_type);
            //产品形态
            if (model.pro_form > -1)
                sourceType.AppendFormat(" AND pro_form={0} ", model.pro_form);
            sb.AppendFormat(@") raction INNER JOIN
            (SELECT folder_id,folder_name,folder_path FROM dbo.T_DocumentFolder) folder ON raction.folder_id=folder.folder_id INNER JOIN
            (SELECT file_id,file_name,alias_name,file_type,file_size=(CASE WHEN file_size>999 THEN CAST(CAST(file_size/1024 AS DECIMAL(18,2)) AS NVARCHAR(20))+'M' ELSE CAST(file_size AS NVARCHAR(20))+'KB' END),
            CONVERT(varchar(100),issue_start,23) AS issue_start,CONVERT(varchar(100),issue_end,23) AS issue_end,CONVERT(varchar(100),create_time,23) AS create_time,CONVERT(varchar(100),approve_time,120) AS approve_time,CONVERT(varchar(100),update_time,120) AS update_time,folder_author,pro_type,dbo.fn_selectdict(pro_type) AS pro_type_text,pro_form,vr_star,folder_limit,
            issue_name,create_name,file_note,attach_url AS url,create_id,doc_type,dbo.fn_selectdict(mater_type) as mater_type,doc_number,file_money,down_number,file_classify,file_status,browse_num,quote_num,play_num,is_recommend,video_url,org_unit,file_code,folder_num,isHot,isSoldOut FROM dbo.T_DocumentInfo WHERE if_remove=0 {0} ", sourceType);
            //【奇点元宇宙】应用、房间
            if (model.vrType == 5)
                sb.Append($" AND page_type='QDMetaProgram' ");
            else if (model.vrType == 6)
                sb.Append($" AND page_type='QDMetaRoom' ");
            //文档名称
            if (!string.IsNullOrEmpty(model.keyword))
                sb.Append(string.Format(" AND (file_name like '%{0}%' OR create_name like '%{0}%' OR file_code like '%{0}%') ", model.keyword));
            if (!string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE)  AND issue_start<=CAST('{1}' AS DATE) ", model.issue_start, model.issue_end));
            else if (!string.IsNullOrEmpty(model.issue_start) && string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE) ", model.issue_start));
            else if (string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_end<=CAST('{0}' AS DATE) ", model.issue_end));
            sb.Append(string.Format(@") info ON raction.file_id = info.file_id)doc WHERE doc.DispOrder>{0} ORDER BY doc.approve_time DESC;", (model.page - 1) * model.rows));
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString(),CommandType.Text);
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<DocTreeResultModel>(dt);
        }
        /// <summary>
        /// 3. 公用文档 数量统计
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int GetTablePublicDocCount(DocTableParamModel model, int user_id, int fileStatus = 5)
        {
            List<int> fList = GetChildFileID(model.folder_id, user_id);
            StringBuilder folder = new StringBuilder();
            sb?.Clear();
            sb.Append(string.Format(@"SELECT COUNT(0) FROM  (SELECT folder_id FROM dbo.T_DocumentFolder WHERE 1=1 "));
            //是否包含子目录
            if (model.inChild.Equals("no"))
            {
                sb.AppendFormat(" AND folder_id in({0}) ", model.folder_id);
                folder.AppendFormat(" AND folder_id in({0}) ", model.folder_id);
            }else
            {
                if (!string.IsNullOrEmpty(model.folder_path))
                {
                    sb.AppendFormat(@" AND folder_path LIKE '{0}%' ", model.folder_path);
                    folder.AppendFormat("  AND folder_path LIKE '{0}%' ", model.folder_path);
                }
                else
                {
                    sb.AppendFormat(" AND folder_id in({0}) ", string.Join(",", fList));
                    folder.AppendFormat(" AND folder_id in({0}) ", string.Join(",", fList));
                }
            }
            sb.AppendFormat(")folder INNER JOIN  (SELECT file_id,folder_id FROM dbo.T_DocumentFolderRelation WHERE 1=1 {0} ", folder.ToString());
            //价格
            StringBuilder sourceType = new StringBuilder();
            if (model.sourceType > 0)
                sourceType.AppendFormat(" AND file_status={0} ", model.sourceType);
            else
                sourceType.AppendFormat(" AND (file_status=3 OR file_status=5)");
            //是否热门推荐
            if (model.isHot > -1)
                sourceType.Append($" AND isHot={model.isHot} ");
            //产品类别
            if (model.pro_type > -1)
                sourceType.AppendFormat(" AND pro_type={0} ", model.pro_type);
            //适合分类
            if (!string.IsNullOrEmpty(model.file_classify))
                sourceType.AppendFormat(" AND file_classify='{0}' ", model.file_classify);
            //是否推荐
            if (model.is_recommend > -1)
                sourceType.AppendFormat(" AND is_recommend={0} ", model.is_recommend);
            //素材格式
            if (!string.IsNullOrEmpty(model.mater_type))
                sourceType.AppendFormat(" AND mater_type='{0}' ", model.mater_type);
            //所属单位
            if (!string.IsNullOrEmpty(model.org_unit))
                sourceType.AppendFormat(" AND unit_code='{0}' ", model.org_unit);
            //产品形态
            if (model.pro_form > -1)
                sourceType.AppendFormat(" AND pro_form={0} ", model.pro_form);
            sb.AppendFormat(@") raction ON folder.folder_id=raction.folder_id INNER JOIN  (SELECT file_id FROM dbo.T_DocumentInfo WHERE if_remove=0 {0} ", sourceType);
            //【奇点元宇宙】应用、房间
            if (model.vrType == 5)
                sb.Append($" AND page_type='QDMetaProgram' ");
            else if (model.vrType == 6)
                sb.Append($" AND page_type='QDMetaRoom' ");
            //文档名称
            if (!string.IsNullOrEmpty(model.keyword))
                sb.Append(string.Format(" AND (file_name like '%{0}%' OR create_name like '%{0}%' OR file_code like '%{0}%') ", model.keyword));
            if (!string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE)  AND issue_start<=CAST('{1}' AS DATE) ", model.issue_start, model.issue_end));
            else if (!string.IsNullOrEmpty(model.issue_start) && string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE) ", model.issue_start));
            else if (string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_end<=CAST('{0}' AS DATE) ", model.issue_end));
            sb.Append(@") info ON raction.file_id = info.file_id;");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text));
        }
        /// <summary>
        ///  3.公用文档 统计自己目录下文档数量
        /// </summary>
        /// <param name="model"></param>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public int GetTablePublicDocCountByOwn(DocTableParamModel model, int user_id, int fileStatus = 5)
        {
            sb?.Clear();
            //奇点元宇宙【房间】统计
            if (model.vrType == 6)
            {
                sb.Append($@"SELECT COUNT(0) FROM dbo.T_DocumentInfo o WHERE if_remove=0 AND file_status=5 AND EXISTS(
	            SELECT file_id FROM dbo.T_DocumentInfo i WHERE if_remove=0 AND file_status=5 AND EXISTS(
	            SELECT file_id FROM dbo.T_DocumentFolderRelation r WHERE 1=1 ");
                if (model.folder_id > 0)
                    sb.Append($" AND r.folder_id={model.folder_id} ");
                sb.Append($"AND i.file_id=r.file_id) AND i.file_id = o.programId)");
            }
            else
            {
                sb.Append(string.Format(@"SELECT COUNT(*) FROM (SELECT file_id FROM dbo.T_DocumentFolderRelation "));
                if (model.folder_id > 0)
                    sb.AppendFormat(" WHERE folder_id={0} ", model.folder_id);
                sb.AppendFormat(@") raction INNER JOIN (SELECT file_id FROM dbo.T_DocumentInfo WHERE if_remove=0 AND file_status={0} ", fileStatus);
                //文档名称
                if (!string.IsNullOrEmpty(model.keyword))
                    sb.Append(string.Format(" AND (file_name like '%{0}%' OR create_name like '%{0}%') ", model.keyword));
                if (!string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                    sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE)  AND issue_start<=CAST('{1}' AS DATE) ", model.issue_start, model.issue_end));
                else if (!string.IsNullOrEmpty(model.issue_start) && string.IsNullOrEmpty(model.issue_end))
                    sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE) ", model.issue_start));
                else if (string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                    sb.Append(string.Format(" AND issue_end<=CAST('{0}' AS DATE) ", model.issue_end));
                sb.Append(@") info ON raction.file_id = info.file_id;");
            }  
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text));
        }
        /// <summary>
        ///  根据folder_id获取子节点fodler_id集合
        /// </summary>
        /// <param name="folder_id"></param>
        /// <returns></returns>
        private List<int> GetChildFileID(int folder_id,int user_id)
        {
            listFolder?.Clear();
            listFolder.Add(folder_id);
            List<T_DocumentFolder> docFolder = new T_DocumentFolderDAL().Select(s => !string.IsNullOrEmpty(s.folder_name)).ToList();
            var tempList = docFolder.Where(s => s.folder_pid == folder_id);
            if (null == tempList || tempList.Count() <= 0)
                return listFolder;
            foreach (T_DocumentFolder item in tempList)
            {
                listFolder.Add(item.folder_id);
                var strList = docFolder.Where(s => s.folder_pid == item.folder_id).ToList();
                if (null != strList || strList.Count() > 0)
                    GetList(docFolder, strList,user_id);
            }
            return listFolder;
        }
        private void GetList(List<T_DocumentFolder> docFolder, List<T_DocumentFolder> strList,int user_id)
        {
            foreach (T_DocumentFolder item in strList)
            {
                listFolder.Add(item.folder_id);
                var tempList = docFolder.Where(s => s.folder_pid == item.folder_id).ToList();
                if (null != strList || strList.Count() > 0)
                    GetList(docFolder, tempList,user_id);
            }
        }
        #endregion

        #region 4. 回收站/下架资源
        /// <summary>
        ///  回收站/下架资源
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<DocTreeResultModel> GetTableRemoveDoc(DocTableParamModel model, int user_id)
        {
            List<int> fList = GetChildFileID(model.folder_id, user_id);
            sb?.Clear();
            sb.Append(string.Format(@"SELECT TOP {0} info.file_id,info.file_name,info.create_id,info.alias_name,info.file_type,info.file_size,info.issue_start,info.issue_end,info.create_time,info.create_name,info.file_note,info.url,info.doc_type,info.mater_type,doc_number,info.file_money,info.file_classify,info.file_status,info.browse_num,info.quote_num,info.play_num,info.is_recommend,info.approve_time,info.update_time,info.folder_author,info.pro_type,info.pro_type_text,info.pro_form,info.vr_star,info.down_number,info.folder_limit,info.org_unit,info.file_code,info.folder_num,folder_id=0,folder_name='',folder_path='' FROM 
            (SELECT file_id FROM T_DocumentFolderRelation WHERE 1=1 ", model.rows));
            if (model.inChild.Equals("no"))
                sb.AppendFormat(" AND folder_id in({0}) ", model.folder_id);
            else
            {
                if (!string.IsNullOrEmpty(model.folder_path))
                    sb.AppendFormat(@" AND folder_path LIKE '{0}%' ", model.folder_path);
                else
                    sb.AppendFormat(" AND folder_id in({0}) ", string.Join(",", fList));
            }
            sb.Append(") del INNER JOIN ");
            if (model.vrType == 6)
                sb.Append($@"(SELECT file_id FROM dbo.T_DocumentInfo WHERE page_type='QDMetaProgram')meto ON meto.file_id = del.file_id INNER JOIN ");
            sb.AppendFormat(@" 
            (SELECT file_id,programId,file_name,alias_name,file_type,file_size=(CASE WHEN file_size>999 THEN CAST(CAST(file_size/1024 AS DECIMAL(18,2)) AS NVARCHAR(20))+'M' ELSE CAST(file_size AS NVARCHAR(20))+'KB' END),
            CONVERT(varchar(100),issue_start,23) AS issue_start,CONVERT(varchar(100),issue_end,23) AS issue_end,CONVERT(varchar(100),create_time,23) AS create_time,CONVERT(varchar(100),approve_time,120) AS approve_time,CONVERT(varchar(100),update_time,120) AS update_time,folder_author,pro_type,dbo.fn_selectdict(pro_type) AS pro_type_text,pro_form,vr_star,folder_limit,
            issue_name,create_name,file_note,attach_url AS url,ROW_NUMBER() OVER(order by file_id asc) as DispOrder,create_id,doc_type,dbo.fn_selectdict(mater_type) as mater_type,doc_number,file_money,down_number,file_classify,file_status,browse_num,quote_num,play_num,is_recommend,video_url,org_unit,file_code,folder_num FROM dbo.T_DocumentInfo WHERE file_status=4 and  if_remove=0 ");
            //【奇点元宇宙】应用、房间
            if (model.vrType ==5)           
                sb.Append($" AND page_type='QDMetaProgram' ");
             else if(model.vrType == 6)
                sb.Append($" AND page_type='QDMetaRoom' ");
            //文档名称
            if (!string.IsNullOrEmpty(model.keyword))
                sb.Append(string.Format(" AND (file_name like '%{0}%' OR create_name like '%{0}%') ", model.keyword));
            if (!string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE)  AND issue_start<=CAST('{1}' AS DATE) ", model.issue_start, model.issue_end));
            else if (!string.IsNullOrEmpty(model.issue_start) && string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE) ", model.issue_start));
            else if (string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_end<=CAST('{0}' AS DATE) ", model.issue_end));
            if (model.vrType == 6)
                sb.Append(string.Format(@")info ON info.programId=meto.file_id WHERE info.DispOrder>{0};", (model.page - 1) * model.rows));
            else
                sb.Append(string.Format(@")info ON del.file_id = info.file_id WHERE info.DispOrder>{0};", (model.page - 1) * model.rows));
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text);
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<DocTreeResultModel>(dt);
        }
        /// <summary>
        /// 4. 回收站 数量统计
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int GetTableRemoveDocCount(DocTableParamModel model, int user_id)
        {
            List<int> fList = GetChildFileID(model.folder_id, user_id);
            sb?.Clear();
            sb.Append(@"SELECT COUNT(*) FROM (SELECT file_id FROM T_DocumentFolderRelation  WHERE 1=1 ");
            if (model.inChild.Equals("no"))
                sb.AppendFormat(" AND folder_id in({0}) ", model.folder_id);
            else
            {
                if (!string.IsNullOrEmpty(model.folder_path))
                    sb.AppendFormat(@" AND folder_path LIKE '{0}%' ", model.folder_path);
                else
                    sb.AppendFormat(" AND folder_id in({0}) ", string.Join(",", fList));
            }
            sb.AppendFormat(@") del INNER JOIN ");
            if (model.vrType == 6)
                sb.Append($@"(SELECT file_id FROM dbo.T_DocumentInfo WHERE page_type='QDMetaProgram')meto ON meto.file_id = del.file_id INNER JOIN ");
            sb.Append(" (SELECT file_id,programId FROM dbo.T_DocumentInfo WHERE file_status=4 and  if_remove=0 ");
            //【奇点元宇宙】应用、房间
            if (model.vrType == 5)
                sb.Append($" AND page_type='QDMetaProgram' ");
            else if (model.vrType == 6)
                sb.Append($" AND page_type='QDMetaRoom' ");
            //文档名称
            if (!string.IsNullOrEmpty(model.keyword))
                sb.Append(string.Format(" AND (file_name like '%{0}%' OR create_name like '%{0}%') ",model.keyword));
            if (!string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE)  AND issue_start<=CAST('{1}' AS DATE) ", model.issue_start, model.issue_end));
            else if (!string.IsNullOrEmpty(model.issue_start) && string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE) ", model.issue_start));
            else if (string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_end<=CAST('{0}' AS DATE) ", model.issue_end));
            if (model.vrType == 6)
                sb.Append(") info ON info.programId=meto.file_id");
            else
                sb.Append(") info ON del.file_id = info.file_id");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text));
        }
        #endregion

        #region 5. 待审核资源
        /// <summary>
        ///  5：待审核资源
        /// </summary>
        /// <param name="model"></param>
        /// <param name="user_id">用户id</param>
        /// <returns></returns>
        public List<DocTreeResultModel> GetTableApproveDoc(DocTableParamModel model, int user_id, string unit_code)
        {
            sb?.Clear();
            sb.Append(string.Format(@"SELECT TOP  {0} info.file_id,info.file_name,info.create_id,info.alias_name,info.file_type,info.file_size,info.issue_start,info.issue_end,info.create_time,info.create_name,info.file_note,info.url,info.doc_type,info.mater_type,doc_number,info.file_money,info.file_classify,info.file_status,info.browse_num,info.quote_num,info.play_num,info.is_recommend,info.approve_time,info.update_time,info.folder_author,info.pro_type,info.pro_type_text,info.pro_form,info.vr_star,info.down_number,info.folder_limit,info.org_unit,info.file_code,info.folder_num,folder_id=0,folder_path='',folder_name='' FROM ", model.rows));
            sb.AppendFormat($@"           
            (SELECT file_id,file_name,alias_name,file_type,file_size=(CASE WHEN file_size>999 THEN CAST(CAST(file_size/1024 AS DECIMAL(18,2)) AS NVARCHAR(20))+'M' ELSE CAST(file_size AS NVARCHAR(20))+'KB' END),
            CONVERT(varchar(100),issue_start,23) AS issue_start,CONVERT(varchar(100),issue_end,23) AS issue_end,CONVERT(varchar(100),create_time,120) AS create_time,CONVERT(varchar(100),approve_time,120) AS approve_time,CONVERT(varchar(100),update_time,120) AS update_time,folder_author,pro_type,dbo.fn_selectdict(pro_type) AS pro_type_text,pro_form,vr_star,folder_limit,
            issue_name,create_name,file_note,attach_url AS url,ROW_NUMBER() OVER(order by file_id asc) as DispOrder,create_id,doc_type,dbo.fn_selectdict(mater_type) as mater_type,doc_number,file_money,down_number,file_classify,file_status,browse_num,quote_num,play_num,is_recommend,video_url,org_unit,file_code,folder_num FROM dbo.T_DocumentInfo WHERE if_remove=0 AND file_status=1 AND page_type='{model.goodType}' ");
            //【奇点元宇宙】应用、房间
            if (model.vrType == 5)
                sb.Append($" AND page_type='QDMetaProgram' ");
            else if (model.vrType == 6)
                sb.Append($" AND page_type='QDMetaRoom' ");
            if (string.IsNullOrEmpty(unit_code))
            {
                //管理员，单位代码
                if (!string.IsNullOrEmpty(model.org_unit))
                    if(model.org_unit.Equals("null"))
                        sb.AppendFormat($" AND ISNULL(unit_code,'')='' ");
                    else
                        sb.AppendFormat($" AND unit_code='{model.org_unit}' ");
            }
            else
                sb.AppendFormat($" AND unit_code='{unit_code}' ");
            //文档名称
            if (!string.IsNullOrEmpty(model.keyword))
                sb.AppendFormat(" AND (file_name like '%{0}%' OR create_name like '%{0}%' OR file_code like '%{0}%') ", model.keyword);
            sb.Append(string.Format(@") info where info.DispOrder>{0};", (model.page - 1) * model.rows));
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text);
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<DocTreeResultModel>(dt);
        }
        /// <summary>
        ///  5：待审核资源 数量统计
        /// </summary>
        /// <param name="model"></param>
        /// <param name="user_id">用户id</param>
        /// <returns></returns>
        public int GetTableApproveDocCount(DocTableParamModel model, int user_id, string unit_code)
        {
            sb?.Clear();
            sb.Append($" SELECT COUNT(0) FROM dbo.T_DocumentInfo WHERE if_remove = 0 AND file_status = 1 AND page_type='{model.goodType}' ");
            //【奇点元宇宙】应用、房间
            if (model.vrType == 5)
                sb.Append($" AND page_type='QDMetaProgram' ");
            else if (model.vrType == 6)
                sb.Append($" AND page_type='QDMetaRoom' ");
            if (string.IsNullOrEmpty(unit_code))
            {
                //管理员，单位代码
                if (!string.IsNullOrEmpty(model.org_unit))
                    if (model.org_unit.Equals("null"))
                        sb.AppendFormat($" AND ISNULL(unit_code,'')='' ");
                    else
                        sb.AppendFormat($" AND unit_code='{model.org_unit}' ");
            }
            else
                sb.AppendFormat($" AND unit_code='{unit_code}' ");
            //文档名称
            if (!string.IsNullOrEmpty(model.keyword))
                sb.AppendFormat(" AND (file_name like '%{0}%' OR create_name like '%{0}%' OR file_code like '%{0}%') ", model.keyword);
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text));
        }
        #endregion

        #region 6. 共享免费+被人分享
        /// <summary>
        ///  免费共享+别人给的分享
        /// </summary>
        /// <param name="model"></param>
        /// <param name="user_id">用户id</param>
        /// <returns></returns>
        public List<DocTreeResultModel> GetPublicDocOrShare(DocTableParamModel model, int user_id, List<int> roles)
        {
            List<int> fList = GetChildFileID(model.folder_id, user_id);
            string _roles = (roles.Count > 0) ? string.Join(",", roles) : "0";
            sb?.Clear();
            sb.Append(string.Format(@"SELECT TOP {0} info.file_id,info.file_name,info.create_id,info.alias_name,info.file_type,info.file_size,info.issue_start,info.issue_end,info.create_time,info.create_name,info.file_note,info.url,info.doc_type,info.mater_type,doc_number,info.file_money,info.file_classify,info.file_status,info.browse_num,info.quote_num,info.play_num,info.is_recommend,info.approve_time,info.update_time,info.folder_author,info.pro_type,info.pro_type_text,info.pro_form,info.vr_star,info.down_number,info.folder_limit,info.org_unit,info.file_code,info.folder_num,folder_id=0,folder_name='',folder_path='' FROM ", model.rows));
            if (!string.IsNullOrEmpty(model.folder_path))
                sb.AppendFormat(@"(SELECT file_id FROM T_DocumentSetType WHERE role_id IN({0})
	                UNION
                    SELECT file_id FROM dbo.T_DocumentInfo WHERE file_status=3
                    UNION
	                SELECT file_id FROM T_DocumentSetType WHERE user_id={1} ", _roles, user_id);
            else if (model.folder_id > 0)
                sb.AppendFormat(@"(SELECT file_id FROM T_DocumentSetType WHERE folder_id={0} AND role_id IN({1})
	                UNION
                    SELECT file_id FROM dbo.T_DocumentInfo WHERE file_status=3 
                    UNION
	                SELECT file_id FROM T_DocumentSetType WHERE folder_id={0} AND user_id={2} ", model.folder_id, _roles, user_id);
            sb.AppendFormat(@") raction INNER JOIN
            (SELECT file_id,file_name,alias_name,file_type,file_size=(CASE WHEN file_size>999 THEN CAST(CAST(file_size/1024 AS DECIMAL(18,2)) AS NVARCHAR(20))+'M' ELSE CAST(file_size AS NVARCHAR(20))+'KB' END),
            CONVERT(varchar(100),issue_start,23) AS issue_start,CONVERT(varchar(100),issue_end,23) AS issue_end,CONVERT(varchar(100),create_time,23) AS create_time,CONVERT(varchar(100),approve_time,120) AS approve_time,CONVERT(varchar(100),update_time,120) AS update_time,folder_author,pro_type,dbo.fn_selectdict(pro_type) AS pro_type_text,pro_form,vr_star,folder_limit,
            issue_name,create_name,file_note,attach_url AS url,ROW_NUMBER() OVER(order by file_id asc) as DispOrder,create_id,doc_type,dbo.fn_selectdict(mater_type) as mater_type,doc_number,file_money,down_number,file_classify,file_status,browse_num,quote_num,play_num,is_recommend,video_url,org_unit,file_code,folder_num FROM dbo.T_DocumentInfo WHERE if_remove=0 ");
            //文档名称
            if (!string.IsNullOrEmpty(model.keyword))
                sb.Append(string.Format(" AND (file_name like '%{0}%' OR create_name like '%{0}%') ", model.keyword));
            if (!string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE)  AND issue_start<=CAST('{1}' AS DATE) ", model.issue_start, model.issue_end));
            else if (!string.IsNullOrEmpty(model.issue_start) && string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE) ", model.issue_start));
            else if (string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_end<=CAST('{0}' AS DATE) ", model.issue_end));
            sb.Append(string.Format(@") info ON raction.file_id = info.file_id WHERE info.DispOrder>{0} ORDER BY info.approve_time DESC;", (model.page - 1) * model.rows));
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text);
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<DocTreeResultModel>(dt);
        }
        /// <summary>
        ///  免费共享+别人给的分享
        /// </summary>
        /// <param name="model"></param>
        /// <param name="user_id">用户id</param>
        /// <returns></returns>
        public int GetPublicDocOrShareCount(DocTableParamModel model, int user_id, List<int> roles)
        {
            List<int> fList = GetChildFileID(model.folder_id, user_id);
            string _roles = (roles.Count > 0) ? string.Join(",", roles) : "0";
            sb?.Clear();
            sb.Append(string.Format(@"SELECT COUNT(*) FROM "));
            if (!string.IsNullOrEmpty(model.folder_path))
                sb.AppendFormat(@"(SELECT file_id FROM T_DocumentSetType WHERE role_id IN({0})
	                UNION
                    SELECT file_id FROM dbo.T_DocumentInfo WHERE file_status=3
                    UNION
	                SELECT file_id FROM T_DocumentSetType WHERE user_id={1} ", _roles, user_id);
            else if (model.folder_id > 0)
                sb.AppendFormat(@"(SELECT file_id FROM T_DocumentSetType WHERE folder_id={0} AND role_id IN({1})
	                UNION
                    SELECT file_id FROM dbo.T_DocumentInfo WHERE file_status=3 
                    UNION
	                SELECT file_id FROM T_DocumentSetType WHERE folder_id={0} AND user_id={2} ", model.folder_id, _roles, user_id);
            sb.AppendFormat(@") raction INNER JOIN  (SELECT file_id FROM dbo.T_DocumentInfo WHERE if_remove=0 ");
            //文档名称
            if (!string.IsNullOrEmpty(model.keyword))
                sb.Append(string.Format(" AND (file_name like '%{0}%' OR create_name like '%{0}%') ", model.keyword));
            if (!string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE)  AND issue_start<=CAST('{1}' AS DATE) ", model.issue_start, model.issue_end));
            else if (!string.IsNullOrEmpty(model.issue_start) && string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE) ", model.issue_start));
            else if (string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
                sb.Append(string.Format(" AND issue_end<=CAST('{0}' AS DATE) ", model.issue_end));
            sb.Append(@") info ON raction.file_id = info.file_id;");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text));
        }
        #endregion

        /// <summary>
        ///  获取用户当天上传数据
        /// </summary>
        /// <param name="user_id">用户ID</param>
        /// <param name="modelType">模块类别</param>
        /// <returns></returns>
        public int GetUpNumByUser(int user_id, string modelType)
        {
            sb?.Clear();
            sb.AppendFormat(@"SELECT COUNT(0) FROM 
            (SELECT folder_id FROM dbo.T_DocumentFolder WHERE page_type={0})folder INNER JOIN
            (SELECT file_id,folder_id FROM dbo.T_DocumentFolderRelation WHERE create_id={1})rel ON rel.folder_id = folder.folder_id INNER JOIN
            (SELECT * FROM dbo.T_DocumentInfo WHERE  if_remove = 0)info ON info.file_id = rel.file_id WHERE CONVERT(varchar(100),info.create_time,23)=CONVERT(varchar(100),GETDATE(),23);",modelType, user_id);
            //_param.Add("@pageType", modelType);
            //sb.AppendFormat("SELECT COUNT(0) FROM dbo.T_DocumentInfo  WHERE create_id={0} AND CONVERT(varchar(100),create_time,23)=CONVERT(varchar(100),GETDATE(),23);", user_id);
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text));
        }
        /// <summary>
        ///  获取产品目录树下的数量
        /// </summary>
        /// <param name="folder_path"></param>
        /// <returns></returns>
        public int GetProDocCount(string folder_path,bool botType=false)
        {
            sb?.Clear();
            sb.AppendFormat("SELECT COUNT(0) AS num FROM (SELECT file_id FROM dbo.T_DocumentInfo WHERE if_remove=0 ");
            if (botType)
                sb.Append(" AND (file_status=4 OR file_status=5) ");
            sb.Append($" )info INNER JOIN (SELECT file_id, folder_path FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE '{folder_path}%') rel ON rel.file_id = info.file_id; ");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text));
        }
        /// <summary>
        ///  获取列表数据：头记
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<DocTreeResultModel> GetProTableList(HeadTableParamModel model)
        {
            sb?.Clear();
            sb.Append(string.Format(@"SELECT TOP {0} * FROM( SELECT  ROW_NUMBER() OVER(order by info.approve_time DESC) as DispOrder,isfree=(CASE WHEN info.fileMoney>0 THEN 1 ELSE 0 END),info.file_id,info.file_name,info.create_id,info.alias_name,info.file_type,info.file_size,info.issue_start,info.issue_end,info.create_time,info.create_name,info.file_note,info.url,info.doc_type,info.mater_type,doc_number,info.file_money,info.file_classify,info.file_status,info.browse_num,info.quote_num,info.play_num,info.is_recommend,info.approve_time,info.update_time,info.folder_author,info.pro_type,info.pro_type_text,info.pro_form,info.vr_star,info.down_number,info.folder_limit,info.org_unit,info.file_code,info.folder_num,folder.* FROM (SELECT file_id,folder_id FROM dbo.T_DocumentFolderRelation WHERE 1=1", model.rows));
            if (!string.IsNullOrEmpty(model.folder_path))
                sb.AppendFormat(@" AND folder_path LIKE '{0}%' ", model.folder_path);
            else
                sb.AppendFormat(" AND folder_id={0} ", model.folder_id);
            sb.AppendFormat(@") raction INNER JOIN
            (SELECT folder_id,folder_name,folder_path FROM dbo.T_DocumentFolder) folder ON raction.folder_id=folder.folder_id INNER JOIN
            (SELECT file_id,file_name,alias_name,file_type,file_size=(CASE WHEN file_size>999 THEN CAST(CAST(file_size/1024 AS DECIMAL(18,2)) AS NVARCHAR(20))+'M' ELSE CAST(file_size AS NVARCHAR(20))+'KB' END),
            CONVERT(varchar(100),issue_start,23) AS issue_start,CONVERT(varchar(100),issue_end,23) AS issue_end,CONVERT(varchar(100),create_time,23) AS create_time,CONVERT(varchar(100),approve_time,120) AS approve_time,CONVERT(varchar(100),update_time,120) AS update_time,folder_author,pro_type,dbo.fn_selectdict(pro_type) AS pro_type_text,pro_form,vr_star,folder_limit,
            issue_name,create_name,file_note,attach_url AS url,create_id,doc_type,dbo.fn_selectdict(mater_type) as mater_type,doc_number,file_money,down_number,file_classify,file_status,browse_num,quote_num,play_num,is_recommend,video_url,org_unit,file_code,folder_num,fileMoney=(SELECT SUM(file_money) FROM dbo.T_DocFileOptions files WHERE files.file_id=T_DocumentInfo.file_id) FROM dbo.T_DocumentInfo WHERE if_remove=0 ");
            //文档名称
            if (!string.IsNullOrEmpty(model.keyword))
                sb.Append(string.Format(" AND file_name like '%{0}%' ", model.keyword));
            //分类
            if (!string.IsNullOrEmpty(model.fileClassify))
                sb.AppendFormat(" AND file_classify='{0}' ", model.fileClassify);
            //状态
            if(model.fileType>-1)
                sb.AppendFormat($" AND file_status={model.fileType}");
            sb.AppendFormat(") info ON raction.file_id = info.file_id ");
            //价格
            if (!string.IsNullOrEmpty(model.fileMoney))
            {
                if (model.fileMoney.Equals("free"))
                    //免费
                    sb.AppendFormat(" WHERE info.fileMoney=0 ");
                else if (model.fileMoney.Equals("money"))
                    //收费
                    sb.AppendFormat(" WHERE info.fileMoney>0 ");
            }
            sb.AppendFormat(")doc WHERE doc.DispOrder>{0} ORDER BY doc.approve_time DESC;", (model.page - 1) * model.rows);
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text);
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<DocTreeResultModel>(dt);
        }
        /// <summary>
        ///   获取列表数据数量：头记
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int GetProCount(HeadTableParamModel model)
        {
            sb?.Clear();
            sb.AppendFormat(@"SELECT COUNT(0) AS num FROM( SELECT info.* FROM (SELECT file_id,folder_id FROM dbo.T_DocumentFolderRelation WHERE 1=1 ");
            if (!string.IsNullOrEmpty(model.folder_path))
                sb.AppendFormat(@" AND folder_path LIKE '{0}%' ", model.folder_path);
            else
                sb.AppendFormat(" AND folder_id={0} ", model.folder_id);
            sb.AppendFormat(@") raction INNER JOIN
            (SELECT folder_id FROM dbo.T_DocumentFolder) folder ON raction.folder_id = folder.folder_id INNER JOIN
            (SELECT file_id, file_name,fileMoney=(SELECT SUM(file_money) FROM dbo.T_DocFileOptions files WHERE files.file_id=T_DocumentInfo.file_id)  FROM dbo.T_DocumentInfo WHERE if_remove = 0 ");
            //文档名称
            if (!string.IsNullOrEmpty(model.keyword))
                sb.Append(string.Format(" AND file_name like '%{0}%' ", model.keyword));
            //分类
            if (!string.IsNullOrEmpty(model.fileClassify))
                sb.AppendFormat(" AND file_classify='{0}' ", model.fileClassify);
            //状态
            if (model.fileType > -1)
                sb.AppendFormat($" AND file_status={model.fileType}");
            sb.AppendFormat(") info ON raction.file_id = info.file_id");
            if (!string.IsNullOrEmpty(model.fileMoney))
            {
                //价格
                if (model.fileMoney.Equals("free"))
                    //免费
                    sb.AppendFormat(" WHERE info.fileMoney=0 ");
                else if (model.fileMoney.Equals("money"))
                    //收费
                    sb.AppendFormat(" WHERE info.fileMoney>0 ");
            }
            sb.AppendFormat(")doc");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text));
        }
        /// <summary>
        ///  批量编辑作品
        /// </summary>
        /// <param name="fileId"></param>
        /// <param name="file_classify"></param>
        /// <param name="org_unit"></param>
        /// <returns></returns>
        public int SaveBatchFile(string fileId, string file_classify, string org_unit,string unit_name)
        {
            sb.AppendFormat($"UPDATE dbo.T_DocumentInfo SET unit_code='{org_unit}',org_unit='{unit_name}',file_classify='{file_classify}' WHERE file_id IN({fileId});");
            return SqlHelper.ExecuteNonQuery(sb.ToString());
        }
        //************************************获取字典******************************************
        /// <summary>
        ///  根据代码获取字典下拉
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public List<ResultSelectVal> GetDocDictByCode(SelectKeyModel code)
        {
            sb?.Clear();
            string langSql = "";
            if (code.dictType.Equals("docGoodType"))
            {
                langSql = code.langCode.Equals("cn") ? "folder_name" : "enLang";
                //商品类型
                sb.Append($"SELECT folder_code AS valObj,textObj={langSql},enLang AS enObj,sm_note AS note FROM dbo.T_SysModule WHERE doc_show=1 ORDER BY sm_order;");
            }
            else
            {
                langSql = code.langCode.Equals("cn") ? "item2.code_name" : "enLang";
                string temp = "item2.code_value AS valObj,enLang AS enObj ";
                sb.AppendFormat($@"SELECT {langSql} AS textObj,{temp},code_note AS note FROM 
            (SELECT item_id FROM dbo.T_ItemCode WHERE item_code = '{code.dictType}')item1 INNER JOIN
            (SELECT code_name, code_value, item_id,enLang, code_order,code_note FROM dbo.T_ItemCodeMenum)item2 ON item2.item_id = item1.item_id
            ORDER BY item2.code_order;");
            }
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text);
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<ResultSelectVal>(dt);
        }

        //************************************APP接口************************************
        /// <summary>
        /// 获取商品详情
        /// </summary>
        /// <param name="file_id"></param>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public GoodsListModel GetAPPGoodsDetail(int file_id, int user_id = 0)
        {
            sb?.Clear();
            sb.Append($@"SELECT *,store_name='',store_id=0,founder_id=0,founder_name='',store_url=''
            ,image_num=(SELECT COUNT(0) FROM T_VRDDCircleRelGood rg WHERE dd_id=good.file_id AND rel_type='image')
            ,video_num=(SELECT COUNT(0) FROM T_VRDDCircleRelGood rg WHERE dd_id=good.file_id AND rel_type='video')
            ,works_num=(SELECT COUNT(0) FROM T_VRDDCircleRelGood rg WHERE dd_id=good.file_id AND rel_type='works')
            ,good.is_get as isPay,ROW_NUMBER() OVER(order by good.update_time DESC) as rowOrder  FROM 
            (SELECT ISNULL(gd.rate,0)AS rate,folder.page_type AS pageType,folder.folder_name,info.*,p.*,fileMoney.file_money,CASE WHEN star.us_id>0 THEN 1 ELSE 0 END AS self_star,
            is_get=(CASE WHEN ISNULL(orders.orderId,'')='' THEN 0 ELSE 1 END),
            is_hold=(CASE WHEN ISNULL(favorite.favoriteId,'')='' THEN 0 ELSE 1 END)   FROM 
            (SELECT folder_id,folder_name,page_type,pro_type as folder_pro_type FROM dbo.T_DocumentFolder WHERE if_sys=1)folder INNER JOIN
            (SELECT folder_id,file_id FROM dbo.T_DocumentFolderRelation WHERE file_id={file_id})rel ON rel.folder_id = folder.folder_id INNER JOIN
            (SELECT file_id,file_name,file_size AS appSize,attach_url AS appID,pag_num AS playerNum,isSoldOut,doc_number,gps_x,gps_y,is_mian,is_batch,store_top,store_id,isEntity as is_entity,out_link,qr_code,is_recommend,alias_name,file_status,allow_edit,file_note,approve_note,vr_star,file_classify,folder_author,CONVERT(varchar(100),create_time,120) AS create_time,CONVERT(varchar(100),update_time,120) AS update_time,create_name,browse_num,file_order,isfree=(SELECT COUNT(0) FROM dbo.T_VRGoodDisiPrice price WHERE T_DocumentInfo.file_id=price.file_id AND price.ori_price>0),
            hold_num,talk_num,down_number,CONVERT(varchar(100),approve_time,20) as approve_time,pro_date as pdate,CONVERT(varchar(100),pro_date,20) as proDate,video_url,folder_limit,org_unit,pro_form,file_code,share_num,create_id,pay_num,
            user_head=(select user_img from t_user where user_id=T_DocumentInfo.create_id),
            picture_id=(select top 1 bag_id from T_DocBagFileRecord where file_id=T_DocumentInfo.file_id)
            ,picture=(select top 1 file_url from T_DocBagFileRecord where file_id=T_DocumentInfo.file_id),option_size=(select top 1 option_size from T_DocFileOptions where file_id=T_DocumentInfo.file_id),dbo.fn_dict(pro_type,'vrProType') as proType,pro_type as works_pro_type,supplyType,spread_url,finish_time,topic_num=(SELECT COUNT(0) FROM dbo.T_VRExperienceWorkMain m WHERE m.file_id=T_DocumentInfo.file_id) FROM dbo.T_DocumentInfo WHERE file_id={file_id} )info ON info.file_id = rel.file_id 
            LEFT JOIN(SELECT MAX(specMoney)AS maxPrice,MIN(specMoney)AS minPrice,fileId={file_id} FROM dbo.T_VRDDSpecPrice WHERE fileId={file_id})p ON info.file_id=p.fileId
            LEFT JOIN (SELECT folder_id,user_level FROM T_VRFounderApply WHERE user_id={user_id})fa ON info.store_id=fa.folder_id
            LEFT JOIN (SELECT file_id,rate,user_level FROM dbo.T_VRGoodDiscount)gd ON gd.file_id=info.file_id AND fa.user_level=gd.user_level
            LEFT JOIN  (SELECT file_id AS orderId FROM dbo.T_VROrderDetail WHERE file_id={file_id} AND order_code IN(SELECT order_code FROM dbo.T_VROrder WHERE user_id={user_id} AND status=2))orders ON info.file_id=orders.orderId  LEFT JOIN 
            (select us_id,file_id from T_DocumentUserStar WHERE create_id={user_id})star ON info.file_id=star.file_id LEFT JOIN 
            (select * from (select ROW_NUMBER() OVER(PARTITION BY file_id order by ori_price) as rowOrder,file_id,op_month,ori_price AS file_money from dbo.T_VRGoodDisiPrice WHERE file_id={file_id})opt where opt.rowOrder=1) fileMoney on info.file_id=fileMoney.file_id  LEFT JOIN
            (SELECT file_id AS favoriteId FROM dbo.T_VRFavoriteGoods WHERE file_id={file_id} AND user_id={user_id})favorite ON info.file_id=favorite.favoriteId LEFT JOIN (SELECT file_id,sum(learn_num)as learn_num,sum(result_ok)as result_ok,sum(result_error)as result_error FROM dbo.T_VRExperienceWorkMain group by file_id)workMain
            ON workMain.file_id=info.file_id) good;");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<GoodsListModel>(dt)?.FirstOrDefault();
        }
        /// <summary>
        ///  获取商品列表
        /// </summary>
        /// <param name="model"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public List<GoodsListModel> GetAPPShopList(ViewGoodsModel model, int userId,out int total)
        {
            //度度圈关联商品、传值时folder_path、roleCode无效
            //dd_id > dd_path > id > folder_path > roleCode
            if (model.dd_id > 0)
            {
                model.folder_path = "";
                model.roleCode = "";
                model.id = 0;
                model.dd_path = "";
            }
            if (!string.IsNullOrEmpty(model.dd_path))
            {
                model.folder_path = "";
                model.roleCode = "";
                model.id = 0;
            }
            if (model.id > 0)
            {
                model.folder_path = "";
                model.roleCode = "";
            }
            if (!string.IsNullOrEmpty(model.folder_path))
                model.roleCode = "";
            string temp = string.Empty, infoPageType = null;
            switch (model.vrType)
            {
                case 1: temp = "VRWorks"; break;
                case 2: temp = "VRModule"; break;
                case 3:temp = "DDCircle"; break;
                case 4: temp = "VRBuilding"; break;
                case 5:
                case 6: temp = "QDMeta"; break;
                case 7: temp = "QDMaterial"; break;//忆度元宇宙素材
                default: temp = "VRManager"; break;
            }
            if (model.vrType == 5)
                infoPageType = "QDMetaProgram";
            else if (model.vrType == 6)
                infoPageType = "QDMetaRoom";
            else
                infoPageType = temp;
            sb?.Clear();
            sb.AppendFormat(@" (SELECT *,good.is_get as isPay, ", model.rows);           
            StringBuilder _sb = new StringBuilder();
            //1.0 类别
            switch (model.goodsType)
            {
                case 2: _sb.AppendFormat("ROW_NUMBER() OVER(order by good.down_number DESC,good.talk_num DESC,hold_num DESC,browse_num DESC ");break;//本月火热【热门】
                case 3:_sb.AppendFormat("ROW_NUMBER() OVER(order by good.approve_time DESC ");break;//最新上架
            }
            string p = "ROW_NUMBER() OVER(order by good.isHot desc, ";
            //1.1 价格排序
            switch (model.priceOrder)
            {
                case 1: _sb.AppendFormat((string.IsNullOrEmpty(_sb.ToString())) ? p + " good.file_money ASC " : " ,good.file_money ASC "); break;//升序
                case 2: _sb.AppendFormat((string.IsNullOrEmpty(_sb.ToString())) ? p + " good.file_money DESC ": " ,good.file_money DESC "); break;//降序
                case 3: _sb.AppendFormat((string.IsNullOrEmpty(_sb.ToString())) ? p + " good.update_time DESC ": " ,good.update_time DESC "); break;//修改时间从新到旧
                case 4: _sb.AppendFormat((string.IsNullOrEmpty(_sb.ToString())) ? p + " good.create_time DESC ": " ,good.create_time DESC "); break;//创建时间从新到旧
                case 5: _sb.AppendFormat((string.IsNullOrEmpty(_sb.ToString())) ? p + " good.file_order DESC ": " ,good.file_order DESC "); break;//序号从大到小
                case 6: _sb.AppendFormat((string.IsNullOrEmpty(_sb.ToString())) ? p + " good.file_order ASC " : " ,good.file_order ASC "); break;//序号从小到大
            }
            if (!string.IsNullOrEmpty(_sb.ToString()))
                sb.AppendFormat(_sb.ToString() + ") as rowOrder");
            else
                sb.AppendFormat("ROW_NUMBER() OVER(order by good.file_id ASC) as rowOrder  ");
            sb.AppendFormat($@"  FROM (SELECT folder.page_type AS pageType,folder.folder_id,folder.folder_name,folder.folder_pro_type,
            image_num=(SELECT COUNT(0) FROM T_VRDDCircleRelGood rg WHERE dd_id=info.file_id AND rel_type='image'),
            pimage_num=(SELECT COUNT(0) FROM T_VRDDCircleRelGood rg WHERE dd_id=info.file_id AND rel_type='p-image'),
            video_num=(SELECT COUNT(0) FROM T_VRDDCircleRelGood rg WHERE dd_id=info.file_id AND rel_type='video'),
            works_num=(SELECT COUNT(0) FROM T_VRDDCircleRelGood rg WHERE dd_id=info.file_id AND rel_type='works'),
            voice_num=(SELECT COUNT(0) FROM T_VRDDCircleRelGood rg WHERE dd_id=info.file_id AND rel_type='audio'),
            --voice_num=(SELECT COUNT(0) FROM T_VRDDCircleUserService v WHERE v.file_id=info.file_id),
            --pay_num=(SELECT COUNT(0) FROM N_Order WHERE OrderState IN(2,4) AND OrderCode IN(
            --SELECT OrderCode FROM N_OrderCommodityInfo WHERE file_id=info.file_id)),
            df.store_name,df.founder_id,df.founder_name,df.store_url,ISNULL(gd.rate,0)AS rate,info.*,workMain.learn_num,workMain.result_ok,workMain.result_error,fileMoney.file_money,
            fileMoney.service_ip,CASE WHEN star.us_id>0 THEN 1 ELSE 0 END AS self_star,is_get=(CASE WHEN ISNULL(orders.orderId,'')='' THEN 0 ELSE 1 END),
            favorite.favoriteId,is_hold=(CASE WHEN ISNULL(favorite.favoriteId,'')='' THEN 0 ELSE 1 END)   FROM  ");
            //判断是否有传递角色代码
            if (string.IsNullOrEmpty(model.roleCode))
                sb.Append($"(SELECT folder_id,folder_name,page_type,pro_type as folder_pro_type FROM dbo.T_DocumentFolder WHERE if_sys=1)folder INNER JOIN ");
            else
                sb.Append($@"(SELECT DISTINCT b.folder_id,b.folder_name,b.page_type,b.pro_type as folder_pro_type FROM dbo.T_DocumentFolder a INNER JOIN
                dbo.T_DocumentFolder b ON b.folder_path LIKE a.folder_path+'%' WHERE a.page_type='{temp}' AND b.page_type='{temp}' AND a.if_remove=0 AND b.if_remove=0 AND a.if_sys=1 AND b.if_sys=1 AND a.folder_id IN(
	                SELECT folder_id FROM dbo.T_DocFolderPermissRelation WHERE per_id IN(
                    SELECT per_id FROM dbo.T_RolePermissRelation WHERE role_id IN(
                    SELECT role_id FROM dbo.T_Role WHERE role_code='{model.roleCode}'))))folder INNER JOIN ");
            sb.Append($"(SELECT folder_id,file_id FROM dbo.T_DocumentFolderRelation WHERE 1=1 ");
            //判断path
            if(model.id > 0)
                sb.AppendFormat($" AND folder_id={model.id} ");
            else  if (!string.IsNullOrEmpty(model.folder_path))
                sb.AppendFormat($" AND folder_path like '{model.folder_path}%'");
            //都不指定查询全部
            if(string.IsNullOrEmpty(model.folder_path) && model.id<=0 && string.IsNullOrEmpty(model.roleCode))
            {
                //全部素材、作品、组件                
                sb.AppendFormat(@" AND file_id IN(SELECT rel.file_id FROM 
                (SELECT file_id,folder_path FROM dbo.T_DocumentFolderRelation)rel INNER JOIN
                (SELECT folder_path FROM dbo.T_DocumentFolder WHERE if_sys=1 ");
                if (model.dd_id <= 0)
                    sb.Append($" AND page_type='{temp}' ");
                sb.Append(@" )folder ON folder.folder_path = rel.folder_path)");
            }
            sb.AppendFormat($@")rel ON rel.folder_id = folder.folder_id INNER JOIN
            (SELECT file_id,file_name,isHot,pay_num,doc_number,gps_x,gps_y,is_mian,is_batch,store_top,store_id,isEntity as is_entity,out_link,qr_code,is_recommend,alias_name,file_status,allow_edit,file_note,approve_note,vr_star,file_classify,folder_author,CONVERT(varchar(100),create_time,120) AS create_time,CONVERT(varchar(100),update_time,120) AS update_time,create_name,browse_num,file_order,isfree=(SELECT COUNT(0) FROM dbo.T_VRGoodDisiPrice price WHERE T_DocumentInfo.file_id=price.file_id AND price.ori_price>0),
            hold_num,talk_num,down_number,CONVERT(varchar(100),approve_time,20) as approve_time,video_url,folder_limit,org_unit,pro_form,file_code,share_num,create_id,
            user_head=(select user_img from t_user where user_id=T_DocumentInfo.create_id),
            picture_id=(select top 1 bag_id from T_DocBagFileRecord where file_id=T_DocumentInfo.file_id)
            ,picture=(select top 1 file_url from T_DocBagFileRecord where file_id=T_DocumentInfo.file_id),option_size=(select top 1 option_size from T_DocFileOptions where file_id=T_DocumentInfo.file_id),dbo.fn_dict(pro_type,'vrProType') as proType,pro_type as works_pro_type,supplyType,spread_url,topic_num=(SELECT COUNT(0) FROM dbo.T_VRExperienceWorkMain m WHERE m.file_id=T_DocumentInfo.file_id) FROM dbo.T_DocumentInfo WHERE  if_remove = 0 AND file_status={model.fileStatus}  AND page_type='{infoPageType}' ");
            //if (string.IsNullOrEmpty(model.folder_path))
            //    sb.Append($" AND page_type='{temp}' ");
            //是否需要查询关联商品
            if (!string.IsNullOrEmpty(model.dd_path))
                sb.Append($" AND file_id in(select rel_id from T_VRDDCircleRelGood where dd_id in(select file_id from T_DocumentFolderRelation where folder_path like '{model.dd_path}%')) ");
            //是否实物
            if (model.is_entity > -1)
                sb.Append($" AND isEntity={model.is_entity} ");
            //是否商铺推荐
            if (model.store_top >-1)
            {
                if (model.store_top>0)
                    sb.Append($" AND store_top=1 ");
                else
                    sb.Append($" AND store_top=0 ");
            }
            //素材格式
            if (!string.IsNullOrEmpty(model.materType))
                sb.Append($" AND mater_type='{model.materType}' ");
            //商品来源
            if (model.supplyType > -1)
                sb.Append($" AND supplyType={model.supplyType} "); 
            //产品类别
            if (model.proType > -1)
                sb.AppendFormat(" AND pro_type={0} ", model.proType);
            switch (model.goodsType)
            {
                case 1: sb.AppendFormat(" AND is_recommend=1 "); break;//推荐
                case 9: sb.AppendFormat(" AND folder_limit in('VIP','SVIP') "); break;//权限
            }
            //产品形态
            if(model.proForm>-1)
                sb.AppendFormat(" AND pro_form={0} ", model.proForm);
            //供应商
            if (!string.IsNullOrEmpty(model.supplier))
                sb.AppendFormat(" AND supplier='{0}' ", model.supplier);
            //关键字
            if (!string.IsNullOrEmpty(model.keyword))
                sb.AppendFormat(" AND (file_name LIKE '%{0}%' OR file_note LIKE '%{0}%') ", model.keyword);
            //商品代码
            if (!string.IsNullOrEmpty(model.fileCode))
                sb.AppendFormat(" AND file_code='{0}' ", model.fileCode);
            //所属单位
            if (!string.IsNullOrEmpty(model.orgUnit))
                sb.AppendFormat(" AND unit_code='{0}' ", model.orgUnit);
            //作品分类
            if (!string.IsNullOrEmpty(model.entryType))
                sb.AppendFormat(" AND file_classify='{0}' ", model.entryType);
            //修改开始时间，修改截止时间
            if (!string.IsNullOrEmpty(model.updateStart) && !string.IsNullOrEmpty(model.updateEnd))
                sb.Append(string.Format(" AND update_time>=CAST('{0}' AS DATE)  AND update_time<=CAST('{1}' AS DATE) ", model.updateStart, model.updateEnd));
            else if (!string.IsNullOrEmpty(model.updateStart) && string.IsNullOrEmpty(model.updateEnd))
                sb.Append(string.Format(" AND update_time>=CAST('{0}' AS DATE) ", model.updateStart));
            else if (string.IsNullOrEmpty(model.updateStart) && !string.IsNullOrEmpty(model.updateEnd))
                sb.Append(string.Format(" AND update_time<=CAST('{0}' AS DATE) ", model.updateEnd));
            //权限
            if (!string.IsNullOrEmpty(model.folder_limit))
                sb.AppendFormat(" AND folder_limit='{0}' ", model.folder_limit);
            sb.Append($")info ON info.file_id = rel.file_id ");
            //获取商铺信息
            sb.Append($@" LEFT JOIN (SELECT folder_name AS store_name,folder_id,founder_id,founder_name,
            folder_url AS store_url FROM dbo.T_DocumentFolder)df ON info.store_id=df.folder_id");
            //获取用户商品折扣信息
            sb.Append($@" LEFT JOIN (SELECT folder_id,user_level FROM T_VRFounderApply WHERE user_id={userId})fa ON info.store_id=fa.folder_id
            LEFT JOIN (SELECT file_id,rate,user_level FROM dbo.T_VRGoodDiscount)gd ON gd.file_id=info.file_id AND fa.user_level=gd.user_level ");
            //判断是否获取关联商品
            if (model.dd_id > 0)
                sb.Append($@" INNER JOIN (SELECT dd_id,rel_id FROM T_VRDDCircleRelGood WHERE dd_id={model.dd_id})rg ON info.file_id=rg.rel_id ");
            //关联统计作品主表
            sb.Append($" LEFT JOIN (SELECT file_id,sum(learn_num)as learn_num,sum(result_ok)as result_ok,sum(result_error)as result_error FROM dbo.T_VRExperienceWorkMain group by file_id)workMain ON info.file_id=workMain.file_id ");
            //关注用户的全部作品
            if (model.goodsType == 10)
            {
                if (model.shopType.Equals("DDCircle"))
                    sb.Append($" INNER JOIN (SELECT *,fan_id as user_id FROM DD_Fans WHERE fan_id={userId})starUser ON starUser.user_id = info.create_id ");
                else
                    sb.Append($" INNER JOIN (SELECT *,user_id=(SELECT user_id FROM dbo.T_User WHERE dbo.T_User.user_guid=T_VLineAttentionUser.by_guid) FROM T_VLineAttentionUser WHERE user_guid='{model.userid}')starUser ON starUser.user_id = info.create_id ");
            }    
            //是否根据创建用户所属单位查询
            if (!string.IsNullOrEmpty(model.unitCode))
                sb.Append($@" INNER JOIN (SELECT user_id FROM dbo.T_User WHERE unit_code='{model.unitCode}')users on info.create_id=users.user_id ");
            //点赞的作品
            if (model.goodsType == 11)
                sb.Append($" INNER JOIN (SELECT file_id FROM T_DocumentUserStar WHERE create_id={userId})starFile ON info.file_id=starFile.file_id ");
            string tempRequest = "code_value";//IP
            if (model.requestType)
                tempRequest = "code_note";//域名
            //是否购买
            sb.AppendFormat(@" LEFT JOIN
            (SELECT DISTINCT file_id AS orderId FROM dbo.N_OrderCommodityInfo WHERE OrderCode IN(SELECT OrderCode FROM dbo.N_Order WHERE OrderCreateUserId={0} AND OrderState IN(2,4) AND EndTime>=GETDATE()))orders ON info.file_id=orders.orderId ", userId);
            sb.AppendFormat($@" LEFT JOIN (select us_id,file_id from T_DocumentUserStar WHERE create_id={userId})star ON info.file_id=star.file_id 
            LEFT JOIN (select * from (select ROW_NUMBER() OVER(PARTITION BY file_id order by file_money) as rowOrder,file_id,file_money,
            service_ip=(SELECT {tempRequest} FROM dbo.T_ItemCodeMenum WHERE item_id IN(SELECT item_id FROM dbo.T_ItemCode WHERE item_code='docFileService') AND code_name=T_DocFileOptions.service_id) from T_DocFileOptions)opt where opt.rowOrder=1) fileMoney on info.file_id=fileMoney.file_id  LEFT JOIN
            (SELECT file_id AS favoriteId FROM dbo.T_VRFavoriteGoods WHERE user_id={userId})favorite ON info.file_id=favorite.favoriteId WHERE 1=1 ) good WHERE 1=1 ");
            //免费共享
            if (model.goodsType == 7)
                sb.AppendFormat($" AND good.isfree=0 ");
            //收费
            else if (model.goodsType == 8)
                sb.AppendFormat(" AND good.isfree>0 ");
            //是否购买
            switch (model.isPay)
            {
                case 0:sb.Append(" AND is_get=0)pay "); break;
                case 1: sb.Append(" AND is_get=1)pay "); break;
                default:sb.Append(" )pay "); break;
            }
            //收藏
            if (model.goodsType == 6)
                sb.AppendFormat(" AND ISNULL(pay.favoriteId,'')<>'' ");
            total = SqlHelper.ExecuteScalarInt32($"SELECT COUNT(0) FROM  {sb}");
            return SqlHelper.ExecuteList<GoodsListModel>($"SELECT TOP {model.rows} * FROM  {sb} WHERE pay.rowOrder>{(model.page - 1) * model.rows}");
            //DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            //return HCQ2_Common.Data.DataTableHelper.DataTableToIList<GoodsListModel>(dt);
        }
        /// <summary>
        ///  获取商品总量
        /// </summary>
        /// <param name="model"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int GetAPPShopCount(ViewGoodsModel model, int userId)
        {
            //度度圈关联商品、传值时folder_path、roleCode无效
            //dd_id > dd_path > id > folder_path > roleCode
            if (model.dd_id > 0)
            {
                model.folder_path = "";
                model.roleCode = "";
                model.id = 0;
                model.dd_path = "";
            }
            if (!string.IsNullOrEmpty(model.dd_path))
            {
                model.folder_path = "";
                model.roleCode = "";
                model.id = 0;
            }
            if (model.id > 0)
            {
                model.folder_path = "";
                model.roleCode = "";
            }
            if (!string.IsNullOrEmpty(model.folder_path))
                model.roleCode = "";
            //全部素材、作品、组件
            string temp = string.Empty, infoPageType=null;
            switch (model.vrType)
            {
                case 1: temp = "VRWorks"; break;
                case 2: temp = "VRModule"; break;
                case 3: temp = "DDCircle"; break;
                case 4: temp = "VRBuilding"; break;
                case 5:
                case 6: temp = "QDMeta"; break;
                case 7: temp = "QDMaterial"; break;//忆度元宇宙素材
                default: temp = "VRManager"; break;
            }
            if (model.vrType == 5)
                infoPageType = "QDMetaProgram";
            else if (model.vrType == 6)
                infoPageType = "QDMetaRoom";
            else
                infoPageType = temp;
            sb?.Clear();
            sb.AppendFormat(@"SELECT COUNT(0) AS docCount FROM 
            (SELECT info.*,is_get=(CASE WHEN ISNULL(orders.orderId,'')='' THEN 0 ELSE 1 END) FROM ");
            //判断是否有传递角色代码
            if (string.IsNullOrEmpty(model.roleCode))
                sb.Append($"(SELECT folder_id FROM dbo.T_DocumentFolder WHERE if_sys=1)folder INNER JOIN ");
            else
                sb.Append($@"(SELECT DISTINCT b.folder_id FROM dbo.T_DocumentFolder a INNER JOIN
                dbo.T_DocumentFolder b ON b.folder_path LIKE a.folder_path+'%' WHERE a.page_type='{temp}' AND b.page_type='{temp}' AND a.if_remove=0 AND b.if_remove=0 AND a.if_sys=1 AND b.if_sys=1 AND a.folder_id IN(
	                SELECT folder_id FROM dbo.T_DocFolderPermissRelation WHERE per_id IN(
                    SELECT per_id FROM dbo.T_RolePermissRelation WHERE role_id IN(
                    SELECT role_id FROM dbo.T_Role WHERE role_code='{model.roleCode}'))))folder INNER JOIN ");
            sb.Append($"(SELECT folder_id,file_id FROM dbo.T_DocumentFolderRelation WHERE 1=1 ");
            if (model.id > 0)
                sb.AppendFormat($" AND folder_id={model.id} ");
            //判断path
            else if (!string.IsNullOrEmpty(model.folder_path))
                sb.AppendFormat($" AND folder_path like '{model.folder_path}%' ");
            //都不指定查全部
            if(string.IsNullOrEmpty(model.folder_path) && model.id<=0 && string.IsNullOrEmpty(model.roleCode))
            {
                sb.AppendFormat(@" AND file_id IN(SELECT rel.file_id FROM 
                (SELECT file_id,folder_path FROM dbo.T_DocumentFolderRelation)rel INNER JOIN
                (SELECT folder_path FROM dbo.T_DocumentFolder WHERE if_sys=1)folder ON folder.folder_path = rel.folder_path) ");
            }
            sb.AppendFormat($@")rel ON rel.folder_id = folder.folder_id INNER JOIN
            (SELECT file_id,create_id,isfree=(SELECT COUNT(0) FROM dbo.T_VRGoodDisiPrice price WHERE T_DocumentInfo.file_id=price.file_id AND price.ori_price>0) FROM dbo.T_DocumentInfo WHERE  if_remove = 0  AND file_status={model.fileStatus} AND page_type='{infoPageType}' ");
            //if (string.IsNullOrEmpty(model.folder_path))
            //    sb.Append($" AND page_type='{temp}' ");
            //是否需要查询关联商品
            if (!string.IsNullOrEmpty(model.dd_path))
                sb.Append($" AND file_id in(select rel_id from T_VRDDCircleRelGood where dd_id in(select file_id from T_DocumentFolderRelation where folder_path like '{model.dd_path}%')) ");
            //是否实物
            if (model.is_entity > -1)
                sb.Append($" AND isEntity={model.is_entity} ");
            //是否商铺推荐
            if (model.store_top > -1)
            {
                if (model.store_top > 0)
                    sb.Append($" AND store_top=1 ");
                else
                    sb.Append($" AND store_top=0 ");
            }           
            //素材格式
            if (!string.IsNullOrEmpty(model.materType))
                sb.Append($" AND mater_type='{model.materType}' ");
            //商品来源
            if (model.supplyType > -1)
                sb.Append($" AND supplyType={model.supplyType} ");
            //产品类别
            if (model.proType > -1)
                sb.AppendFormat(" AND pro_type={0} ", model.proType);
            switch (model.goodsType)
            {
                case 1: sb.AppendFormat(" AND is_recommend=1 "); break;//推荐
                case 9: sb.AppendFormat(" AND folder_limit in('VIP','SVIP') "); break;//权限
            }
            //产品形态
            if(model.proForm>-1)
                sb.AppendFormat(" AND pro_form={0} ", model.proForm);
            //供应商
            if (!string.IsNullOrEmpty(model.supplier))
                sb.AppendFormat(" AND supplier='{0}' ", model.supplier);
            //关键字
            if (!string.IsNullOrEmpty(model.keyword))
                sb.AppendFormat(" AND (file_name LIKE '{0}%' OR file_note LIKE '{0}%') ", model.keyword);
            //商品代码
            if (!string.IsNullOrEmpty(model.fileCode))
                sb.AppendFormat(" AND file_code='{0}' ", model.fileCode);
            //所属单位
            if (!string.IsNullOrEmpty(model.orgUnit))
                sb.AppendFormat(" AND unit_code='{0}' ", model.orgUnit);
            //作品分类
            if (!string.IsNullOrEmpty(model.entryType))
                sb.AppendFormat(" AND file_classify='{0}' ", model.entryType);
            //修改开始时间，修改截止时间
            if (!string.IsNullOrEmpty(model.updateStart) && !string.IsNullOrEmpty(model.updateEnd))
                sb.Append(string.Format(" AND update_time>=CAST('{0}' AS DATE)  AND update_time<=CAST('{1}' AS DATE) ", model.updateStart, model.updateEnd));
            else if (!string.IsNullOrEmpty(model.updateStart) && string.IsNullOrEmpty(model.updateEnd))
                sb.Append(string.Format(" AND update_time>=CAST('{0}' AS DATE) ", model.updateStart));
            else if (string.IsNullOrEmpty(model.updateStart) && !string.IsNullOrEmpty(model.updateEnd))
                sb.Append(string.Format(" AND update_time<=CAST('{0}' AS DATE) ", model.updateEnd));
            //权限
            if (!string.IsNullOrEmpty(model.folder_limit))
                sb.AppendFormat(" AND folder_limit='{0}' ", model.folder_limit);
            sb.Append($" )info ON info.file_id = rel.file_id ");
            //判断是否获取关联商品
            if (model.dd_id > 0)
                sb.Append($"  INNER JOIN (SELECT dd_id,rel_id FROM T_VRDDCircleRelGood WHERE dd_id={model.dd_id})rg ON info.file_id=rg.rel_id  ");
            //关注用户的全部作品
            if (model.goodsType == 10)
            {
                if (model.shopType.Equals("DDCircle"))
                    sb.Append($" INNER JOIN (SELECT *,fan_id as user_id FROM DD_Fans WHERE fan_id={userId})starUser ON starUser.user_id = info.create_id ");
                else
                    sb.Append($" INNER JOIN (SELECT *,user_id=(SELECT user_id FROM dbo.T_User WHERE dbo.T_User.user_guid=T_VLineAttentionUser.by_guid) FROM T_VLineAttentionUser WHERE user_guid='{model.userid}')starUser ON starUser.user_id = info.create_id ");
            }
            //是否根据创建用户所属单位查询
            if (!string.IsNullOrEmpty(model.unitCode))
                sb.Append($@" INNER JOIN (SELECT user_id FROM dbo.T_User WHERE unit_code='{model.unitCode}')users on info.create_id=users.user_id ");
            //点赞的作品
            if (model.goodsType == 11)
                sb.Append($" INNER JOIN (SELECT file_id FROM T_DocumentUserStar WHERE create_id={userId})starFile ON info.file_id=starFile.file_id ");
            //是否根据创建用户所属单位查询
            if (!string.IsNullOrEmpty(model.unitCode))
                sb.Append($@" INNER JOIN (SELECT user_id FROM dbo.T_User WHERE unit_code='{model.unitCode}')users on info.create_id=users.user_id ");
            //是否购买
            sb.Append($@" LEFT JOIN (SELECT DISTINCT file_id AS orderId FROM dbo.N_OrderCommodityInfo WHERE OrderCode IN(SELECT OrderCode FROM dbo.N_Order WHERE OrderCreateUserId={userId} AND OrderState IN(2,4)  AND EndTime>=GETDATE()))orders ON info.file_id=orders.orderId ");
            sb.AppendFormat(@" LEFT JOIN
            (SELECT file_id AS favoriteId FROM dbo.T_VRFavoriteGoods WHERE user_id={0})favorite ON info.file_id=favorite.favoriteId WHERE 1=1 ",userId);
            //收藏
            if (model.goodsType == 6)
                sb.AppendFormat(" AND ISNULL(favorite.favoriteId,'')<>'' ");
            //免费共享
            if (model.goodsType == 7)
                sb.AppendFormat($" AND info.isfree=0 ");
            //收费
            else if (model.goodsType == 8)
                sb.AppendFormat(" AND info.isfree>0 ");
            //是否购买
            switch (model.isPay)
            {
                case 0: sb.AppendFormat(")good WHERE good.is_get=0;"); break;
                case 1: sb.AppendFormat(")good WHERE good.is_get=1;"); break;
                default: sb.AppendFormat(")good;"); break;
            }
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  获取我的资源/作品(分页)
        /// </summary>
        /// <param name="folderId"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public List<GoodsListModel> GetSynchronMyVR(DocListParam model, int userId)
        {
            string docType = !string.IsNullOrEmpty(model.docType) ? $" AND page_type='{model.docType}' " : "";
            sb?.Clear();
            sb.AppendFormat($@"SELECT TOP {model.rows} * FROM 
            (SELECT info.*,rel.folder_id,folder.folder_pro_type,ROW_NUMBER() OVER(ORDER BY info.file_id ASC)AS rowNumer,is_get=(CASE WHEN ISNULL(orders.orderId,'')='' THEN 0 ELSE 1 END),is_hold=(CASE WHEN ISNULL(favorite.favoriteId,'')='' THEN 0 ELSE 1 END)   FROM (SELECT folder_id,pro_type as folder_pro_type  FROM dbo.T_DocumentFolder WHERE if_sys=1 ");
            if (model.proType > 0)
                sb.Append($" AND pro_type={model.proType} ");
            sb.Append($@")folder INNER JOIN
            (SELECT folder_id,file_id FROM dbo.T_DocumentFolderRelation WHERE 1=1");
            if (model.docStatus != 7 && model.docStatus != 8)
                sb.AppendFormat(" AND create_id={0} ", userId);
            //path
            if (!string.IsNullOrEmpty(model.folder_path))
                sb.AppendFormat(@" AND folder_path LIKE '{0}%' ", model.folder_path);
            else if (model.folder_id > 0)
                sb.AppendFormat(" AND folder_id={0} ", model.folder_id);
            if (!string.IsNullOrEmpty(model.roleCode))
            {
                //根据角色配置查询
                sb.Append($@" AND folder_id in(SELECT folder_id FROM dbo.T_DocFolderPermissRelation WHERE per_id IN(
	            SELECT per_id FROM dbo.T_RolePermissRelation WHERE role_id IN(
	            SELECT role_id FROM dbo.T_Role WHERE role_code='{model.roleCode}'))) ");
            }
            sb.AppendFormat($@")rel ON rel.folder_id = folder.folder_id INNER JOIN ");
            string mater_type = !string.IsNullOrEmpty(model.mater_type) ? $" AND mater_type='{model.mater_type}' " : "";
            string tempSql = @"file_id,file_name,isEntity as is_entity,alias_name,file_status,allow_edit,approve_note,file_note,vr_star,file_classify,folder_author,CONVERT(varchar(100),create_time,120) AS create_time,CONVERT(varchar(100),update_time,120) AS update_time,create_name,browse_num,file_order,isfree=(SELECT COUNT(0) FROM dbo.T_VRGoodDisiPrice price WHERE T_DocumentInfo.file_id=price.file_id AND price.ori_price>0),
                    hold_num,talk_num,down_number,CONVERT(varchar(100), approve_time, 20) as approve_time,video_url,folder_limit,org_unit,pro_form,file_code,share_num,create_id,
                    user_head = (select user_img from t_user where user_id = T_DocumentInfo.create_id) ,
                    picture_id=(select top 1 bag_id from T_DocBagFileRecord where file_type=0 and file_id=T_DocumentInfo.file_id ORDER BY create_date DESC),
                    picture = (select top 1 file_url from T_DocBagFileRecord where file_type=0 and file_id = T_DocumentInfo.file_id ORDER BY create_date DESC),
                    option_size = (select top 1 option_size from T_DocFileOptions where file_id = T_DocumentInfo.file_id),dbo.fn_dict(pro_type, 'vrProType') as proType,pro_type as works_pro_type ,supplyType,spread_url,page_type as pageType";
            #region 全部
            if (model.docStatus == -1)
            {
                //                sb.Append($@"(SELECT file_id,file_name,file_note,file_money,file_classify,CONVERT(varchar(100),create_time,23) AS create_time,create_name,browse_num,hold_num,talk_num,down_number,CONVERT(varchar(100),approve_time,120) AS approve_time,file_status,spread_url,video_url FROM dbo.T_DocumentInfo WHERE if_remove=0 AND create_id={userId} {mater_type} {docType}
                //                  UNION 
                //                  SELECT file_id,file_name,file_note,file_money,file_classify,CONVERT(varchar(100),create_time,23) AS create_time,create_name,browse_num,
                //hold_num,talk_num,down_number,CONVERT(varchar(100),approve_time,120) AS approve_time,file_status,spread_url,video_url FROM dbo.T_DocumentInfo WHERE if_remove=0 {docType} AND file_id IN(SELECT file_id FROM T_DocumentSetType WHERE user_id={userId}) {mater_type}
                //                    )info ON info.file_id = rel.file_id ");
                sb.Append($@"(SELECT {tempSql} FROM dbo.T_DocumentInfo WHERE if_remove=0 AND create_id={userId} {mater_type} {docType}
                  UNION 
                  SELECT {tempSql} FROM dbo.T_DocumentInfo WHERE if_remove=0 {docType} AND file_id IN(SELECT file_id FROM T_DocumentSetType WHERE user_id={userId}) {mater_type}
                    )info ON info.file_id = rel.file_id ");
            }
            #endregion

            #region 收到分享
            else if (model.docStatus == 7)
                //收到分享  
                //                sb.Append($@"(SELECT file_id,file_name,file_note,file_money,file_classify,CONVERT(varchar(100),create_time,23) AS create_time,create_name,browse_num,
                //hold_num,talk_num,down_number,CONVERT(varchar(100),approve_time,120) AS approve_time,file_status,spread_url,video_url FROM dbo.T_DocumentInfo WHERE if_remove=0 {docType} AND file_id IN(SELECT file_id FROM T_DocumentSetType WHERE user_id={userId}) {mater_type}) info ON info.file_id = rel.file_id ");
                sb.Append($@"(SELECT {tempSql} FROM dbo.T_DocumentInfo WHERE if_remove=0 {docType} AND file_id IN(SELECT file_id FROM T_DocumentSetType WHERE user_id={userId}) {mater_type}) info ON info.file_id = rel.file_id ");
            #endregion

            #region 自己的
            else
                //sb.Append($@"(SELECT file_id,file_name,file_note,file_money,file_classify,CONVERT(varchar(100),create_time,23) AS create_time,create_name,browse_num,hold_num,talk_num,down_number,CONVERT(varchar(100),approve_time,120) AS approve_time,file_status,spread_url,video_url FROM dbo.T_DocumentInfo WHERE if_remove=0 {docType} AND file_status={(model.docStatus == 8 ? 1 : model.docStatus)} AND create_id={userId} {mater_type}) info ON info.file_id = rel.file_id ");
                sb.Append($@"(SELECT {tempSql} FROM dbo.T_DocumentInfo WHERE if_remove=0 {docType} AND file_status={(model.docStatus == 8 ? 1 : model.docStatus)} AND create_id={userId} {mater_type}) info ON info.file_id = rel.file_id ");
            #endregion

            //名称查询
            if (!string.IsNullOrEmpty(model.fileName))
                sb.AppendFormat(" AND info.file_name LIKE '%{0}%' ", model.fileName);
            sb.AppendFormat(@" LEFT JOIN
            (SELECT file_id AS orderId FROM dbo.T_VROrderDetail WHERE order_code IN(SELECT order_code FROM dbo.T_VROrder WHERE user_id={0} AND status=2))orders ON info.file_id=orders.orderId LEFT JOIN
            (SELECT file_id AS favoriteId FROM dbo.T_VRFavoriteGoods WHERE user_id={0})favorite ON info.file_id=favorite.favoriteId
            )doc WHERE doc.rowNumer>{1};", userId, (model.page - 1) * model.rows);
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<GoodsListModel>(dt);
        }
        /// <summary>
        ///  统计我的资源/作品 数量
        /// </summary>
        /// <param name="model"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int GetSynchronMyVRCount(DocListParam model, int userId)
        {
            string docType = !string.IsNullOrEmpty(model.docType) ? $" AND page_type='{model.docType}' " : "";
            sb?.Clear();
            sb.AppendFormat(@"SELECT COUNT(0) FROM (SELECT folder_id FROM dbo.T_DocumentFolder WHERE if_sys=1 ");
            if (model.proType > 0)
                sb.Append($" AND pro_type={model.proType} ");
            sb.Append($@")folder INNER JOIN  (SELECT folder_id,file_id FROM dbo.T_DocumentFolderRelation WHERE 1=1 ");
            if (model.docStatus != 7 && model.docStatus!=8)
                sb.AppendFormat(" AND create_id={0} ", userId);
            //path
            if (!string.IsNullOrEmpty(model.folder_path))
                sb.AppendFormat(@" AND folder_path LIKE '{0}%' ", model.folder_path);
            else if (model.folder_id > 0)
                sb.AppendFormat(" AND folder_id={0} ", model.folder_id);
            //角色代码
            if (!string.IsNullOrEmpty(model.roleCode))
            {
                //根据角色配置查询
                sb.Append($@" AND folder_id in(SELECT folder_id FROM dbo.T_DocFolderPermissRelation WHERE per_id IN(
	            SELECT per_id FROM dbo.T_RolePermissRelation WHERE role_id IN(
	            SELECT role_id FROM dbo.T_Role WHERE role_code='{model.roleCode}'))) ");
            }
            sb.AppendFormat(@")rel ON rel.folder_id = folder.folder_id INNER JOIN ");
            string mater_type = !string.IsNullOrEmpty(model.mater_type) ? $" AND mater_type='{model.mater_type}' " : "";
            #region 全部
            if (model.docStatus == -1)
            {
                sb.Append($@"(SELECT file_id FROM dbo.T_DocumentInfo WHERE if_remove=0 AND create_id={userId} {mater_type} {docType}
                  UNION 
                  SELECT file_id FROM dbo.T_DocumentInfo WHERE if_remove=0 {docType} AND file_id IN(SELECT file_id FROM T_DocumentSetType WHERE user_id={userId}) {mater_type}
                    )info ON info.file_id = rel.file_id ");
            }
            #endregion

            #region 收到分享
            else if (model.docStatus == 7)
                //收到分享  
                sb.Append($@"(SELECT file_id FROM dbo.T_DocumentInfo WHERE if_remove=0 {docType} AND file_id IN(SELECT file_id FROM T_DocumentSetType WHERE user_id={userId}) {mater_type}) info ON info.file_id = rel.file_id ");
            #endregion

            #region 自己的
            else
                sb.Append($@"(SELECT file_id FROM dbo.T_DocumentInfo WHERE if_remove=0 {docType} AND file_status={(model.docStatus == 8 ? 1 : model.docStatus)} AND create_id={userId} {mater_type}) info ON info.file_id = rel.file_id ");
            #endregion

            //名称查询
            if (!string.IsNullOrEmpty(model.fileName))
                sb.AppendFormat(" AND info.file_name LIKE '%{0}%' ", model.fileName);
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  判断用户是否购买过此商品
        /// </summary>
        /// <param name="fileId"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int PayCountByFileId(int fileId, int userId)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0) FROM dbo.N_OrderCommodityInfo WHERE OrderCode IN
            (SELECT OrderCode FROM dbo.N_Order WHERE OrderCreateUserId = {userId}
            AND OrderState IN(2, 4) AND (EndTime >= GETDATE() or ISNULL(EndTime,'')='')) AND file_id = {fileId};");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  判断文件是否有被分享给用户没有
        /// </summary>
        /// <param name="fileId"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int ShareFileToUser(int fileId, int userId)
        {
            sb?.Clear();
            sb.AppendFormat(@"select count(0) as shareCount from T_DocumentSetType where file_id={0} and user_id={1} or role_id in(select role_id from T_UserRoleRelation where user_id={1});", fileId, userId);
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  统计审核权限
        /// </summary>
        /// <param name="perId"></param>
        /// <returns></returns>
        public int IsApproveLimit(List<int> perId)
        {
            sb?.Clear();
            sb.AppendFormat(@"SELECT COUNT(0) FROM dbo.T_Permissions WHERE per_type='docSystem' AND per_id IN({0});", string.Join(",", perId));
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
       
        /// <summary>
        ///  获取附件地址
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public PayDocFileOptionsModel GetBagFileRecord(int file_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT info.file_name,record.file_url,options.option_name,optionUrl=menu.code_value+options.option_url FROM 
            (SELECT file_id,file_name FROM dbo.T_DocumentInfo WHERE file_id={file_id})info LEFT JOIN
            (SELECT file_id,file_url FROM dbo.T_DocBagFileRecord)record ON record.file_id = info.file_id LEFT JOIN
            (SELECT file_id,option_name,service_id,option_url FROM dbo.T_DocFileOptions)options ON info.file_id=options.file_id LEFT JOIN
            (SELECT code_name,code_value FROM dbo.T_ItemCodeMenum WHERE item_id IN(SELECT item_id FROM dbo.T_ItemCode WHERE item_code='docFileService'))menu ON 
            options.service_id=menu.code_name;");
            DataTable fileOptionInfo = SqlHelper.ExecuteDataTable(sb.ToString());
            if (fileOptionInfo == null || fileOptionInfo.Rows.Count <= 0)
                return null;
            PayDocFileOptionsModel result = new PayDocFileOptionsModel { FileName = Helper.ToString(fileOptionInfo.Rows[0]["file_name"]), FileImgUrl = Helper.ToString(fileOptionInfo.Rows[0]["file_url"]) };
            List<PayDocFileOptionsChild> fileOptions = new List<PayDocFileOptionsChild>();
            foreach (DataRow drow in fileOptionInfo.Rows)
            {
                fileOptions.Add(new PayDocFileOptionsChild
                {
                    OptionName = Helper.ToString(drow["option_name"]),
                    OptionUrl = Helper.ToString(drow["optionUrl"])
                });
            }
            result.DocFileOptions = fileOptions;
            return result;
        }
        /// <summary>
        ///  获取商品购买使用截止时间
        /// </summary>
        /// <param name="file_id"></param>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public PayNorderModel GetFileEndTime(int file_id, int user_id)
        {
            PayNorderModel result = new PayNorderModel(true);
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0) AS price,
            endTime=(SELECT TOP 1 CONVERT(varchar(100),EndTime,120) FROM dbo.N_Order WHERE OrderCreateUserId={user_id} AND OrderCode IN(
	            SELECT OrderCode FROM dbo.N_OrderCommodityInfo WHERE file_id={file_id}) ORDER BY EndTime DESC)
             FROM dbo.T_VRGoodDisiPrice WHERE file_id={file_id} AND ori_price>0;");
            DataTable fileOptionInfo = SqlHelper.ExecuteDataTable(sb.ToString());
            if (fileOptionInfo == null || fileOptionInfo.Rows.Count <= 0)
                return result;
            foreach (DataRow drow in fileOptionInfo.Rows)
            {
                if(Helper.ToInt(drow["price"])==0)
                    return result;
                if (string.IsNullOrEmpty(Helper.ToString(drow["endTime"])))
                {
                    result.isPay = false;
                    return result;
                }
                else
                    result.endTime = Helper.ToString(drow["endTime"]);
            }
            return result;
        }
        /// <summary>
        ///  获取作品集合的附件信息
        /// </summary>
        /// <param name="fileIds"></param>
        /// <returns></returns>
        public List<DelRecordOption> GetFildDelRecord(List<int> fileIds)
        {
            if (fileIds == null || fileIds.Count <= 0)
                return null;
            sb?.Clear();
            sb.Append($@"SELECT doc.option_url,code.code_value FROM 
            (SELECT file_id,option_url,service_id FROM dbo.T_DocFileOptions WHERE file_id IN({string.Join(",", fileIds.ToArray())}))doc INNER JOIN
            (SELECT code_name,code_value FROM dbo.T_ItemCodeMenum WHERE item_id=(SELECT item_id FROM dbo.T_ItemCode WHERE item_code='docFileService')) code ON doc.service_id=code.code_name;");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<DelRecordOption>(dt);
        }
        /// <summary>
        ///  判断是否有合法的购买记录，以及是否在有效期
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public int CheckDelFile(int file_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0) AS num FROM 
            (SELECT TOP 1 EndTime FROM dbo.N_Order WHERE OrderCode IN
            (SELECT OrderCode FROM dbo.N_OrderCommodityInfo WHERE file_id = {file_id}) AND(OrderState = 2 OR OrderState = 4) ORDER BY EndTime DESC)orders
            WHERE orders.EndTime >= GETDATE(); ");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  获取用户收藏的商品和作品
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<UserFavoriteList> GetUserFavorite(UserFavorite param,int user_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.rows} a.file_id,a.file_name FROM( 
             SELECT i.*,ROW_NUMBER() OVER(ORDER BY create_date DESC)AS num FROM 
             (SELECT file_id,create_date FROM dbo.T_VRFavoriteGoods WHERE user_id={user_id})f INNER JOIN
             (SELECT file_id,file_name FROM dbo.T_DocumentInfo WHERE page_type IN('VRWorks','DDCircle'))i ON i.file_id = f.file_id
             )a WHERE a.num>{(param.page - 1) * param.rows};");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<UserFavoriteList>(dt);
        }
        /// <summary>
        ///  获取用户浏览历史
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<UserBrowseResult> GetUserBrowse(InputUserBrowseParam param)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.rows} * FROM (
            SELECT CONVERT(varchar(100),create_date,120) AS create_date,b_type,id=(CASE WHEN file_id>0 THEN file_id ELSE folder_id END),
            name=(CASE WHEN file_id>0 THEN (SELECT file_name FROM dbo.T_DocumentInfo WHERE file_id=g.file_id) ELSE 
            (SELECT folder_name FROM dbo.T_DocumentFolder WHERE folder_id=g.folder_id) END),ROW_NUMBER() OVER(ORDER BY create_date DESC)AS num 
            FROM dbo.T_VRDDUserBrowseLog g WHERE userid='{param.userid}' ");
            if (param.b_type > -1)
                sb.Append($" AND b_type={param.b_type} ");
            //来源
            if (!string.IsNullOrEmpty(param.feed_source))
                sb.Append($" AND g.b_source='{param.feed_source}' ");
            sb.Append($" )l WHERE l.num>{(param.page - 1) * param.rows} ");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<UserBrowseResult>(dt);
        }
        /// <summary>
        ///  获取我的收藏商品列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<MyGoodsFavorite> GetMyGoodsFavorite(InputMyGoods param,int user_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.rows} * FROM( 
            SELECT i.*,r.folderID,r.folderName,ROW_NUMBER() OVER(ORDER BY f.create_date DESC)AS num FROM 
            (SELECT file_id,create_date FROM dbo.T_VRFavoriteGoods WHERE user_id={user_id})f INNER JOIN 
            (SELECT file_id AS fileID,file_name AS fileName,file_note AS fileNote,store_id AS storeID,
            (SELECT folder_name FROM dbo.T_DocumentFolder WHERE folder_id=o.store_id)AS storeName,o.hold_num AS holdNum,
             o.pay_num AS payNum FROM dbo.T_DocumentInfo o WHERE if_remove=0)i ON f.file_id=i.fileID INNER JOIN
            (SELECT file_id,folder_id AS folderID,(SELECT folder_name FROM dbo.T_DocumentFolder WHERE folder_id=n.folder_id)AS folderName 
            FROM dbo.T_DocumentFolderRelation n)r ON i.fileID=r.file_id) g WHERE g.num>{(param.page - 1) * param.rows} ORDER BY g.num;");
            return SqlHelper.ExecuteList<MyGoodsFavorite>(sb.ToString());
        }
        /// <summary>
        ///  获取店铺商品列表
        /// </summary>
        /// <param name="param"></param>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public List<OutputGoodsByStore> GetGoodsByStoreList(InputGoodsByStore param, int user_id, out int total)
        {
            sb?.Clear();
            sb.Append("SELECT ROW_NUMBER() OVER(ORDER BY ");
            //3=修改时间降序；4=创建时间降序；5=序号降序；6=序号升序
            switch (param.priceOrder)
            {
                case 1: sb.Append($" info.filePrice  ASC"); break;//价格升序
                case 2: sb.Append($" info.filePrice DESC "); break;//价格降序
                case 4: sb.Append($" info.create_time DESC "); break;
                case 5: sb.Append($" info.file_order DESC "); break;
                case 6: sb.Append($" info.file_order ASC "); break;
                case 7: sb.Append($" info.payNum DESC "); break;//销量(付款人数)降序
                case 8: sb.Append($" info.payNum ASC "); break;//销量(付款人数)升序
                default: sb.Append($" info.update_time DESC "); break;//3
            }
            sb.Append($@")AS num,info.*,f.* FROM 
            (SELECT file_id,folder_id FROM dbo.T_DocumentFolderRelation r WHERE  folder_path LIKE '{param.folderPath}%' ");
            if (param.folderId > 0)
                sb.Append($" AND folder_id={param.folderId} ");
            sb.Append($@")rel INNER JOIN  
            (SELECT file_id,isSoldOut,create_time,update_time,file_order,file_name AS fileName,file_note AS fileNote,
            gps_x AS gpsX,gps_y AS gpsY,doc_number AS floor,vUrl as VideoUrl,
            fileImage=(SELECT TOP 1 file_url FROM T_DocBagFileRecord WHERE file_id=i.file_id AND file_type=0),
            audioNum=(SELECT COUNT(0) FROM T_VRDDCircleRelGood rg WHERE dd_id=i.file_id AND rel_type='audio'),
            is_mian AS isMian,is_batch AS isBatch,isEntity,is_recommend AS isRecommend,store_top AS isStroeTop,
            browse_num AS browseNum,hold_num AS holdNum,vr_star AS starNum,pay_num AS payNum,store_id AS storeID,
            storeName=(SELECT folder_name FROM dbo.T_DocumentFolder WHERE folder_id=i.store_id),
            isHold=(SELECT CAST(COUNT(0) AS BIT) FROM T_VRFavoriteGoods WHERE file_id=i.file_id AND user_id={user_id}),
            isStar=(SELECT CAST(COUNT(0) AS BIT) FROM T_DocumentUserStar WHERE file_id=i.file_id AND create_id={user_id}),
            filePrice=(SELECT MIN(specMoney) FROM dbo.T_VRDDSpecPrice WHERE fileId=i.file_id),
            maxPrice=(SELECT MAX(specMoney) FROM dbo.T_VRDDSpecPrice WHERE fileId=i.file_id),
            rate0=ISNULL((SELECT TOP 1 rate FROM dbo.T_VRGoodDiscount WHERE file_id=i.file_id AND user_level=0),0),
            rate=(SELECT ISNULL(MIN(rate),100) FROM dbo.T_VRGoodDiscount WHERE file_id=i.file_id)
            ,sense=dbo.GetDistance({param.gpsX},{param.gpsY},gps_x,gps_y)
             FROM dbo.T_DocumentInfo i WHERE page_type='DDCircle' AND file_status=5 AND if_remove=0 ");
            //是否推荐
            if (param.isPush > -1)
                sb.Append($" AND is_recommend={param.isPush} ");
            //是否热门推荐
            if (param.isHot > -1)
                sb.Append($" AND isHot={param.isHot} ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (file_name LIKE '%{param.keyword}%' OR file_note LIKE '%{param.keyword}%') ");
            if (param.stroeTop > -1)
                sb.Append($" AND store_top=1 ");
            sb.Append($@")info ON rel.file_id=info.file_id LEFT JOIN
            (SELECT folder_id AS folderID,folder_name AS folderName,founder_id AS founderID,founder_name AS 
            founderName,founderPhone=(SELECT TOP 1 user_phone FROM dbo.T_User WHERE user_id=df.founder_id)
             FROM dbo.T_DocumentFolder df)f ON rel.folder_id=f.folderID)a ");
            //记录总量
            total= Helper.ToInt(SqlHelper.ExecuteScalar($"SELECT COUNT(0) FROM ( {sb.ToString()}"));
            return SqlHelper.ExecuteList<OutputGoodsByStore>($"SELECT TOP {param.rows} * FROM ( {sb.ToString()} WHERE a.num>{(param.page - 1) * param.rows}");
        }
        /// <summary>
        ///  获取商品规格信息
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public List<OutputSpecInfo> GetSpecInfo(int file_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT m.specName,c.specOption FROM 
            (SELECT specID,specName FROM T_VRDDSpecMain)m INNER JOIN 
            (SELECT a.specID,specOption=
            (SELECT STUFF((SELECT ';'+optionName FROM dbo.T_VRDDSpecChild
	            WHERE fileId=a.fileId AND specID=a.specID FOR XML PATH('')),1,1,''
            )) FROM dbo.T_VRDDSpecChild a WHERE a.fileId={file_id} GROUP BY a.fileId,a.specID)c
            ON m.specID=c.specID;");
            return SqlHelper.ExecuteList<OutputSpecInfo>(sb.ToString());
        }
    }
}